6

I am building a system to bring in shapefile data from a PostGIS database and render it on top of OpenStreetMap tiles using the Leaflet.js library.

My table (sierra_county_parcels) has a polygon geometry field (geom) in SRID 4326, and a field called "upc" that contains the parcel ID. There are about 16,000 rows in sierra_county_parcels. What I need to do is retrieve all of the upc values for rows where the geom polygon is within the currently visible part of the Leaflet map control. I have already written the code to send the northwest and southeast lat/lon pairs and generate an SQL query, which looks like this:

SELECT upc AS pt_parcel_key
FROM  sierra_county_parcels
WHERE  ST_Within(geom, ST_Envelope(ST_GeomFromText('MULTIPOINT(33.167444534375925 -107.14931488037108, 33.08909661229697 -107.35513687133788)')))

The MULTIPOINT WKT is what I'm currently using to specify the corners (nw_latitude, nw_longitude, se_latitude, se_longitude).

The problem is that the query always returns 0 results. For this query, the map was zoomed out to show most of the region where the geometries should be.

Any ideas?

Thanks in advance,

John W.

  • What if a polygon is not completely within your box and just a part of it is intersecting? – EZMapdesign Apr 21 '13 at 9:51
6

Your x's and y's are reversed. Longitude is X and comes first, Latitude is Y and comes second.

  • Ended up changing the query a bit to use the && operator and BOX3D too. But yes, my lat/longs were indeed switched. Thanks much for your help! – jwillis Apr 4 '13 at 18:40
2

Try using ST_MakeEnvelope to make a box:

SELECT upc AS pt_parcel_key
FROM sierra_county_parcels
WHERE geom && ST_MakeEnvelope($long_min, $lat_min, $long_max, $lat_max, 4326);

(note: replace the $ parameters with the appropriate value)

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.