[GIS] Finding row that has shapefile containing lat/long point in PostGIS geometry column

latitude longitudepoint-in-polygonpostgis

I am fairly new to PostGIS and I'm trying to return a row in my table that has the shapefile containing a lat/long point I pass in via st_contains. Below is a query I wrote myself that brings back 0 rows with no errors as seen below.

SELECT * 
FROM es_zones 
WHERE ST_Contains(geom, ST_GeomFromText('POINT(-73.952545 40.774576)'))=true;

Having found a query similar of that to which I am trying to accomplish here, below is my last query ran.

SELECT * 
FROM es_zones 
WHERE ST_Contains(es_zones.geom,ST_transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',26918),26918))=true 
AND ST_Distance(es_zones.geom,ST_transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',21918),21918))=false;

which also brings back 0 rows as seen below

----+-------------+-------+--------+--------------+-------------+--------------+-------------+---------------+---------------+-----------+-----+----------+-------+--------

gid | schooldist | boro | initials | creat_date | edit_date | zoned_dist | boro_num | shape_leng | shape_area | remarks | dbn | esid_no | label | geom 

(0 rows)

Really not sure where I am going wrong.

I was able to get QGIS set up and connected to my PostGIS data. I don't understand what the 997487, 221261 coordinates are.

How do they correspond to the lat/lon points I got back from my GeoCoder Gem?

I passed them in as my point but got the following error;

ERROR: Operation on mixed SRID geometries

QGIS view of PostGIS data

Verifying the SRID shows

SELECT ST_SRID(geom) FROM es_zones LIMIT 1;
 st_srid 
---------
  926918
 (1 row)

So it sounds like something may have happened to my spacial_ref_sys table, or perhaps I didn't get it configured correctly to begin with. A Google search for my last error ERROR: GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys returned this StackOverflow question. Which points to running spatial_ref_sys.sql to regenerate the rows in that table.

I found some elaboration on how to go about executing spatial_ref_sys.sql in the PostGIS documentation here, but I'm confused why it says it can't find SRID 4326 when looking in PGAdmin I can see it as shown below!

enter image description here

Best Answer

The input data is in EPSG 26918 (UTM zone 18N). So if you want to query against that, you need to use that spatial reference system. If you want to ask in longitude / latitude, you need to transform that into 26918, which is easy to do with ST_Transform.

The conversion looks like:

SELECT ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918)

So if you want to find any area that contains that particular point:

SELECT * FROM es_zones WHERE ST_Contains(geom, ST_Transform(ST_GeometryFromText('POINT(-73.952545 40.774576)',4326), 26918))
Related Question