1

I have a table with 1000 points where the latlng coords are in GCS_North_American_1983. I transformed/changed/set the SRID/coordinate system to 2252 (state plane feet of central michigan) with the below queries.

SELECT ST_SetSRID(geom,2252) from drugpoints;
SELECT UpdateGeometrySRID('drugpoints','geom',2252);
ALTER TABLE drugpoints
   ALTER COLUMN geom 
   TYPE Geometry(point, 2252) 
   USING ST_Transform(geom,2252);
Update drugpoints set geom= st_transform(geom,2252);
select ST_AsEWKT(geom) from drugpoints;

enter image description here

I am attempting to buffer all these points using the st_buffer with a 1000 foot buffers. when I perform the below query and bring it into QGIS it gives me one enormous circle around all of the points(with 1000 records)

select *, st_buffer(geom, 1000) into drugs2 from drugpoints;

when I pull it into QGIS it looks like this

enter image description here

when I run the buffer tool on the drugpoints in arcgis with the coordinate system of 2252 it correctly buffers each point with a 1000 foot buffers

enter image description here

*running it postgres

My question: why does the drugpoints shapefile lose the coordinate system units of 2252 (which I clearly set/defined/transformed...) when I use the st_buffer function on it? Am I missing a coordinate system query within the buffer query?

  • Where are you running this query? How many record are returned? What does ST_SRID(geom) return (should be 2252 if your data is in state plane) – DPSSpatial Sep 9 '16 at 23:31
  • running it postgres, and the st_srid(geom) does return 2252, and 1000 records are being returned – ziggy Sep 9 '16 at 23:34
  • I figured that when I changed and transformed the CS on the drugpoints layer when I perform the st_buffer on the geometry the state plan feet would hold up, but that is not the case – ziggy Sep 9 '16 at 23:37
  • When you say " a coordinate system of 2252 (state plane feet of central michigan) but with latlong coordinates" do you mean that the point values really are in WGS84 lat/long, but the geometry is set to 2252? Or vice versa? Can you show a sample of the data (e.g. EWKT for the geometry you have) to make this clearer? – BradHards Sep 10 '16 at 1:35
  • 1
    So you've got the concept wrong. When you did the ST_SRID(..., 2252) you have just changed the declared SRID, not the positions. You probably want to use postgis.net/docs/ST_FlipCoordinates.html to make long/lat data and to set 4326 (assuming your data really is WGS-84). Then do an ST_Transform to get the data into your state plane. Then do the buffer (which is in the coordinate system specified in the source data). Do each of these as separate steps to check it really is producing what you want. – BradHards Sep 10 '16 at 4:54
1

This query made set the SRID to 4269 of x,y coordinates from a table into another table. then I used the st_transform function to change those coordinates into michigan state plane feet for further correct calculations

select name, type_, ST_Transform(ST_SetSRID(ST_MakePoint(x, y), 4269),2252) as geom into projecteddrugpoints from drugpoints;

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.