2

I am fairly new to PostGIS and I'm trying to return a row in my table that has the shape file 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. Any help is appreciated.

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

  • Welcome to gis.SE. If you were hoping to get something in Manhattan, your WKT is incorrect - you're asking for Antarctica. Try putting the longitude first, then the latitude) - its an X-Y thing. – BradHards Jan 19 '14 at 4:18
  • @BradHards thanks for clarifying. I made those changes in both instances and added ST_ to my GeometryFromText and Transform methods. Both were getting flagged. Still getting empty return from both though =# SELECT * FROM es_zones WHERE ST_Contains(es_zones.geom,ST_transform(ST_GeometryFromText('POINT(73.952545 40.774576)',26918),26918))=true; =# 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(73952545 40.774576)',21918),21918))=0.00; – Brad W Jan 19 '14 at 5:47
  • Please edit the question (just click edit below the question) rather than posting comments. The syntax at the top of your original question looks OK (a bit strange, but not wrong). Hint: you probably want a negative longitude, and probably a decimal place. – BradHards Jan 19 '14 at 6:24
  • I had tried the longitude previously as both a negative and positive value. The Ruby gem Geocoder gives it back to me as a negative in degrees decimal. A quick Google search shows it as a positive value. – Brad W Jan 19 '14 at 15:36
  • Are you sure the data is in WGS84 longitude/latitude? What does SELECT ST_SRID(geom) FROM es_zones LIMIT 1; show? What does SELECT As_EWKT(geom) FROM es_zones LIMIT 1; show? – BradHards Jan 19 '14 at 20:35
3

After extensive discussion on the question, I think we're finally there.

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))
  • This is very close to the query I started with, except maybe with the lat lon switched. This returns ERROR: GetProj4StringSPI: Cannot find SRID (4326) in spatial_ref_sys. I have the return from ST_AsEWKT. It starts with SRID=926918;MULTIPOLYGON((( with a long array of two point numbers of the format xxxxxx.xxxxxxxxxx xxxxxx.xxxxxxxxxx,....))) – Brad W Jan 20 '14 at 1:50
  • What does SELECT * FROM spatial_ref_sys WHERE srid=4326; show? Do you have a database cleaner? stackoverflow.com/questions/20031811/… might be helpful. – BradHards Jan 20 '14 at 5:01
  • SELECT * FROM spatial_ref_sys WHERE srid=4326 returns; srid | auth_name | auth_srid | srtext | proj4text ------+-----------+-----------+--------+----------- (0 rows) I don't have a database cleaner. But I had seen the Stackoverflow question you posted above. It is what lead me to check the spatial_ref_sys table via the pgAdmin tool – Brad W Jan 20 '14 at 5:09
  • It makes no sense for SELECT * FROM spatial_ref_sys WHERE srid=4326 to return no rows but for there to be equivalent rows in PGAdmin. I have no idea what sort of problem could have caused that though - are you confident that your whole postgis setup is correct? – BradHards Jan 20 '14 at 5:45
  • This is my first time working with GIS data and setting up the PostGIS extension. At this point there is very little I am completely confident in. However as I posted, and you have noted, there it is in PGAdmin. – Brad W Jan 20 '14 at 13:00
1

I would suggest taking a look at your dataset visually (you could connect to your database using QGIS for example). That way you can check if a point would actually be in the polygon, and as @BradHards suggests, check that you have the correct lon/lat when constructing the geometry.

Your query looks fine - it's not necessary to have true at the end. You can just do something like this:

SELECT * 
FROM es_zones 
WHERE ST_Contains(geom, ST_GeomFromText('POINT(lon lat)'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.