Need SQL syntax for ... I have a table with individual polygons like a jigsaw puzzle. Given a Lon Lat, I need to find the polygon where the lon lat in located. I am trying:

select * from "parcels_06003" where "geom" @ ('-120.0077874119654', '38.71168052297362')

geom is my polygon column in the db.


Use for example ST_Within:

SELECT * FROM "parcels_06003"
WHERE ST_Within (ST_PointFromText('POINT(-120.0077874119654 38.71168052297362)', 4326), geom);

You were vague on how you provide lat/lon, so the point construction may need tweaking. The 4326 is the SRID of the WGS84 projection.

Continuing from the comments, your projection is SR-ORG:15, very similar to 4269, so you can try with 4269 first or follow the PostGIS insertion link on the page to add it (if it isn't present already as 915).

| improve this answer | |
  • it is 4326 projection, but I got this error: ERROR: Operation on mixed SRID geometries ********** Error ********** ERROR: Operation on mixed SRID geometries SQL state: XX000 – jim Jun 13 '12 at 21:01
  • how do I veryify the project for this imported table? – jim Jun 13 '12 at 21:06
  • you mean your table is also using 4326? The error says that both geometries need to use the same projection, so if you're taking the points from another table with a different projection, they need to be reprojected first. – lynxlynxlynx Jun 13 '12 at 21:11
  • See postgis.org/documentation/manual-2.0/ST_Transform.html for how to do that. – lynxlynxlynx Jun 13 '12 at 21:13
  • I mean, when the file is uploaded it defaults to 4236 proj. How do find out what the source file project is? – jim Jun 13 '12 at 21:21

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.