3

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?

7
  • 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, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 3:07

1 Answer 1

6

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';
4
  • 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, 2015 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, 2015 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.
    – Regina Obe
    Nov 5, 2015 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, 2015 at 15:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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