CODEWITHSUNDEEP
pythonpostgresqlpostgispsycopg2
e7lT2P
e7lT2P

Reputation: 1931

Points inside polygon in PostGIS

I have a table samplecol that contains (a sample):

vessel_hash  | status | station | speed |  latitude   |  longitude  | course | heading |        timestamp         |                      the_geom                      
--------------+--------+---------+-------+-------------+-------------+--------+---------+--------------------------+----------------------------------------------------
 103079215239 | 99     | 841     | 5     | -5.41844510 | 36.12160900 | 314    | 511     | 2016-06-12T06:31:04.000Z | 0101000020E61000001BF33AE2900F424090AF4EDF7CAC15C0
 103079215239 | 99     | 3008    | 0     | -5.41778710 | 36.12144900 | 117    | 511     | 2016-06-12T06:43:27.000Z | 0101000020E6100000E2900DA48B0F424042C3AC61D0AB15C0
 103079215239 | 99     | 841     | 17    | -5.42236900 | 36.12356900 | 259    | 511     | 2016-06-12T06:50:27.000Z | 0101000020E610000054E6E61BD10F42407C60C77F81B015C0
 103079215239 | 99     | 841     | 17    | -5.41781710 | 36.12147900 | 230    | 511     | 2016-06-12T06:27:03.000Z | 0101000020E61000004D13B69F8C0F424097D6F03ED8AB15C0
 103079215239 | 99     | 841     | 61    | -5.42201900 | 36.13256100 | 157    | 511     | 2016-06-12T06:08:04.000Z | 0101000020E6100000CFDC43C2F71042409929ADBF25B015C0
 103079215239 | 99     | 841     | 9     | -5.41834020 | 36.12225000 | 359    | 511     | 2016-06-12T06:33:03.000Z | 0101000020E6100000CFF753E3A50F42408D68965F61AC15C0

I try to fetch all points inside polygon with:

poisInpolygon = """SELECT col.vessel_hash,col.longitude,col.latitude,
            ST_Contains(ST_GeomFromEWKT('SRID=4326; POLYGON((-15.0292969 47.6357836,-15.2050781 47.5172007,-16.2597656 29.3821751, 35.0683594 26.1159859, 38.0566406 47.6357836,-15.0292969 47.6357836))'),
            ST_GeomFromEWKT(col.the_geom)) FROM samplecol As col;"""

The output is:

(103079215291L, Decimal('40.87123100'), Decimal('29.24107000'), False) 

(103079215291L, Decimal('40.86702000'), Decimal('29.23967000'), False) 

(103079215291L, Decimal('40.87208200'), Decimal('29.22113000'), False) 

(103079215291L, Decimal('40.86973200'), Decimal('29.23963000'), False) 

(103079215291L, Decimal('40.87770800'), Decimal('29.20229900'), False)

I don't figure out what is False in the results. Is this the correct way or am I doing something wrong?

Also this code uses the INDEX in the field the_geom?

Upvotes: 1

Views: 1823

Answers (1)

Jim Jones
Jim Jones

Reputation: 19693

The query returns false because all points from your sample are outside of the given polygon. Here an overview of your points (somewhere in the northeast of Tanzania) and polygon (south Europe and north Africa):

enter image description here

To test your query, I added another point somewhere in Málaga, which is inside of your polygon, and it returned true just as expected (last geometry in the insert statement as EWKT). This is the script:

CREATE TEMPORARY TABLE t (the_geom GEOMETRY);
INSERT INTO t VALUES ('0101000020E61000001BF33AE2900F424090AF4EDF7CAC15C0'),
             ('0101000020E6100000E2900DA48B0F424042C3AC61D0AB15C0'),
             ('0101000020E610000054E6E61BD10F42407C60C77F81B015C0'),
             ('0101000020E61000004D13B69F8C0F424097D6F03ED8AB15C0'),
             ('0101000020E6100000CFDC43C2F71042409929ADBF25B015C0'),
             ('0101000020E6100000CFF753E3A50F42408D68965F61AC15C0'),
             (ST_GeomFromEWKT('SRID=4326;POINT(-4.4427 36.7233)'));

And here is your query: 

db=# SELECT 
ST_Contains(ST_GeomFromEWKT('SRID=4326; POLYGON((-15.0292969 47.6357836,-15.2050781 47.5172007,-16.2597656 29.3821751, 35.0683594 26.1159859, 38.0566406 47.6357836,-15.0292969 47.6357836))'),
            ST_GeomFromEWKT(col.the_geom)) 
FROM t As col;

st_contains 
-------------
 f
 f
 f
 f
 f
 f
 t
(7 Zeilen)

Btw: storing the same coordinates as GEOMETRY and as NUMERIC is totally redundant. You might want to get rid of the columns latitude and longitude and extract their values with ST_X and ST_Y on demand.

Upvotes: 2

Related Questions