Matching buffer in Openlayers and same buffer in PostGIS query

Question

I am using a buffer tool to create a buffer on my base map in Openlayers. In addition to creating and visualizing this buffer, I pass the center point and radius to a query. The query collects all features of a certain table which fall into this buffer, and shows them on the map.

The problem is that in these two cases buffers are generated separately and are not exactly the same. This causes the query to collect less (not all) features inside the buffer. Below is an image to clarify the situation. You can see the buffer created in Openlayers in Orange color, as well as features inside it that are collected from PostGIS in black and gold colors (different features).

the circular buffer in OpenLayers and the ellipsoid shape of results retrieved from PostGIS query

Its easy to see how much difference is between these two buffers and the one that is generated by the query is more like an ellipsoid rather than circle.

How can I solve this?

The code for generating buffer in Openlayers:

var sides = 40;
var new_geom = OpenLayers.Geometry.Polygon.createGeodesicPolygon(centroid, radius, sides, 45, projection);

and I use the following WHERE clause inside my SELECT query:

WHERE ST_DWithin(ST_Transform(h.geom, 26918), (SELECT ST_Transform(ST_GeomFromText($center_buffer, 900913), 26918)), $radius_buffer)

more information: h is the alias of my table where I want to collect features that fall inside the circular buffer. and center_buffer and radius_buffer are the variables pass by OpenLayers (the same used for creating the buffer in OpenLayers).

---

As mentioned by a site user below, the city data is around Hamburg and therefore I changed my SRID in my query from 26918 to 26931. With this, the problem of strange ellipsoid shape of my features inside buffer is solved but there is still a big difference between the buffer and the data retrieved from PostGIS. See image below:

enter image description here

Answer 1

Generally, I would expect a buffer in UTM to match up pretty well to a geodetic buffer. Close enough you wouldn't be able to see the difference. But, looking at your map, it looks like you're working in a European city, but you're generating your PostGIS buffer in UTM 18, which is valid in the area of New York City.

UTM projections get worse the further east or west you get from their central meridians, and you are very far east indeed of the center of UTM 18.

Try using the ST_DWithin(geography, geography, radius) function instead.

Or, if your work is always in one region, just figure out the right UTM zone for your region (Zone 31 for Hamburg, ESPG:26931) and keep using ST_DWithin(geometry,geometry,radius).

I also don't like your SQL syntax, BTW, try something without subqueries:

SELECT * FROM something 
WHERE 
 ST_DWithin(
 ST_Transform(h.geom, 26931), 
 ST_Transform(ST_GeomFromText($center_buffer, 900913), 26931), 
 $radius_buffer
 )

Unless you have a function reprojected index on the geometry column, this will also get slow as the table gets big, so you'll want to:

CREATE INDEX something_utm_gix ON something USING GIST (ST_Transform(geom, 26931))

• Catlover: Thank you for the tips. As I updated in my question, I changed the SRID value in PostGIS query. The problem is still not solved. You can see the new image updated in my question above. Any other suggestions on how to fix this?
• Catlover: I also tried using geography function instead, which failed to show the data on map. The WHERE clause of my query is below:
• Catlover: WHERE ST_DWithin(h.geom::geography, ST_GeomFromText($center_buffer, 900913)::geography, $radius_buffer)
• Paul Ramsey: You cannot cast a 900913 geometry into geography, so if you ran this at the command-line you'd get an error.