Selecting lat | long | name with given "polygon(,,,,,,,,)" in PostGIS using Python

Question

My table in PostGIS is this with name samplecol:

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

Output query will must be like this:

name_city  |  latitude   |  longitude  
-----------+-------------+----------
newyork    |-5.41844510  | 36.12160900
pensilvenia|-5.42201900  | 36.13256100
jorjia     |-5.41834020  | 36.12225000

My code is this:

poisInpolygon = """SELECT samplecol.latitude,samplecol.name_city samplecol.longitude
FROM samplecol
WHERE ST_Contains(samplecol.the_geom,('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))'));"""

cursor.execute(poisInpolygon)
exists1 = cursor.fetchall()
count1 = 0;
for ex1 in exists1:
   count1 = count1+1
   print ex1,"\n"
   print "points", count1

I try this code and query but return 0.

What is the problem?

Answer 1

You are checking if a Polygon is inside a Point!

ST_Contains(A, B) checks if

Geometry A contains Geometry B [...] - docs

Switch the arguments to ST_Contains:

SELECT samplecol.name_city,
       samplecol.latitude,
       samplecol.longitude
FROM   samplecol
WHERE  ST_Contains(
         ST_SetSRID(
           '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))'::GEOMETRY,
           <SRID>
         ),
         samplecol.the_geom
       )
;

Note that you need to set <SRID> to the same CRS of samplecol.geom.