2

I have a shapefile of all US States that I've imported into a table with a multipolygon geometry column. (SRID 4326)

I perform a spatial query based on lat/lon to return the state abbreviation. This normally works just fine. Today a query came in for Hawaii and the query returned nothing. So naturally I opened the table up in QGIS and queried the point's coordinates. Sure enough it returned the correct result. I clicked identify and it most certainly had the correct attributes.

The query I'm using is:

SELECT states.state_abbr FROM states WHERE st_contains(states.geom, ST_SetSRID(ST_MakePoint(-159.717,21.933), 4326)) 

Does anything look wrong with that query? I tried st_intersects as well.

5

Sounds like the point just isn't in the polygon.

WITH pt AS ( SELECT ST_SetSRID(ST_MakePoint(-159.717,21.933), 4326) AS geom )
SELECT ST_Distance(states.geom, pt.geom) FROM states, pt 
WHERE states.abbr = 'HI';

And Google seems to think so too. It's close, but it's not in.

https://maps.google.ca/maps?client=safari&oe=UTF-8&q=21.933N+159.717W

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.