3

I am new to PostGIS and QGIS. I am trying to convert geom to geog and then cast it back to geom in order to buffer my streets by 1,000 ft with this query:

SELECT a.gid, (st_buffer(a.geom::geography,304.8)::geometry) as geom, a.street_ext
FROM public.orangeburgroads a
JOIN public.orangeburg b ON st_within(st_centroid(a.geom), b.geom)
LIMIT 1000;

And here is what I am getting:

enter image description here

Does anyone have a clue why I am getting this result?

FYI orangeburg is a municipal boundary and orangeburgroads is a street center-line file.

enter image description here

  • SELECT ST_AsText(ST_Centroid(geom)) FROM orangeburgroads LIMIT 1; – Paul Ramsey Oct 5 '16 at 12:23
  • You are joining the buffers with the output of ST_Within. You are not keeping the original record ids and therefore the query joins buffer 1 with the first element from the output from ST_Within. That brings your whole allocation in disorder. – Matte Oct 5 '16 at 12:31
  • 1
    Just curious: Why are you converting to geog? It shouldn't be needed, at least not in this way. – tilt Oct 5 '16 at 12:37
  • @tilt I am converting so that I can use meters as a buffer unit instead of degrees. – Matt Oct 5 '16 at 13:00
  • 1
    Took me a while to see that the output was in the screenshot ;-) Your points already seem to be in a meters grid, so no need to do a conversion. What happens if you do just st_buffer(a.geom,304.8) ? – tilt Oct 5 '16 at 16:16
4

The coordinates of your data are not geographic, they are Stateplane, SC, Feet, EPSG:3361. I know this because I used your test coordinate and back-projected it to geographics, to see if I landed in Orangeburg:

SELECT ST_AsText(ST_Transform(
  'SRID=3361;POINT(1940865 660341)'::geometry, 4326));

Fix your data, first of all (do this for all the tables where you have projected coordinates, but an SRID of 4326):

ALTER TABLE orangeburgroads 
  ALTER COLUMN geom 
  TYPE Geometry(MultiLineString, 3361)
  USING ST_SetSRID(geom, 3361);

Now, knowing that your coordinates are in feet, just do your buffer query with that knowledge and skipping the trip into geography:

SELECT 
  a.gid, 
  ST_Buffer(a.geom,304.8) AS geom, 
  a.street_ext
FROM public.orangeburgroads a
JOIN public.orangeburg b 
ON ST_Intersects(ST_Centroid(a.geom), b.geom)
LIMIT 1000;

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.