2

I'm pretty new with PostGIS. I have a parcel layer and a census block layer in a postgres database, and I would like to be able to query out all census blocks within a specified distance of a specific parcel. I found this example statement, but I'm not sure how to reference the specific parcel in the query, say by parcel ID?

SELECT roads.roadname, pois.poiname
FROM roads INNER JOIN pois 
ON ST_DWithin(roads.geog, pois.geog, 1609);`

This example uses the geography type, but my layers don't have that. They are in EPSG 3857; can I use the geometry type and get accurate results?

  • Please edit the question to specify the coordinate system(s) (SRID) of the tables. You should also take a stab at attempting a WHERE clause, or there is no way we'll be able to help you further (not knowing what the possible attributes are or which attribute value you require). – Vince Nov 5 '15 at 1:10
  • I dont think the coordinate system matters if I use ST_DWithin with geog, from what I understand it default to meters, but the layers are in 3857. I just need to understand the proper syntax of how to reference a specific feature, let's say I want to reference a parcel that has an id of 123 in a field called ID. I'm not sure how to apply the where clause at this stage. – kflaw Nov 5 '15 at 1:16
  • Please edit the question in response to requests for clarification. It's not fair to those who would help you to make them sift through comments looking for critical information. You may have a fundamental flaw in your spatial constraint, since Web Mercator should not ever be used for ground distance calculations. Another example here – Vince Nov 5 '15 at 2:43
  • I understand, I did edit the question to include the SRID. Are you saying that even if I use geog if my data is in 3857 there could be issue? I will review the links, thanks for the information. – kflaw Nov 5 '15 at 3:00
  • Only one SRID is possible with the geometry type -- 4326 (aka GCS_WGS_1984). Please edit the question to include the fact that you're using a geography type, and to remove the incorrect SRID. – Vince Nov 5 '15 at 3:07
5

Assuming you are using geography type which I am guessing from geog name, query to find all census blocks within 1609 meters ~ 1 mile) of parcel denoted by key '12345'.

SELECT census_blocks.*
   FROM census_blocks INNER JOIN parcels 
      ON ST_DWithin(census_blocks.geog, parcels.geog, 1609)
WHERE parcels.parcel_id = '12345';
  • based on vince's post, I am wondering if I may have an issue with my data being in EPSG 3857. Would it make a difference if I use geography vs geometry? – kflaw Nov 5 '15 at 3:04
  • I edited my original post, I am not using geography type though that was in the example statement I found. – kflaw Nov 5 '15 at 3:12
  • You can always figure out the units by looking in spatial_ref_sys: SELECT proj4text, srtext FROM spatial_ref_sys where srid = 3587; So looks like that is for Michigan and unit is in meters. If you are not in Michigan, then that's not the right spatial reference to be using. – LR1234567 Nov 5 '15 at 12:53
  • actually the SRID is 3857 which is web mercator. I'm wondering if I need to transform the data before doing this query. – kflaw Nov 5 '15 at 15:29

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.