I have a PostgreSQL database with circle shaped objects called 'spots'. Each of those objects has latitude, longitude and a variable radius, information that I need to define this circles. Given an specific coordinate, I select all the objects that contain that given point.

The thing is that I want to switch to PostGIS this stuff because of performance. I've started documenting about PostGIS but as a complete newbie I still have some doubts about data types.

What is the best way to store this kind of information in PostGIS? I don't need precision, because the radius can only have a limited lenght of 100km. I've thought about creating a polygon using ST_Buffer and save this into a geometry field in the 'spots' table. I also do not need an exact circle, a decagon is enough. The test code I'm using to know if this is meeting my needs is the following

create table spots (name varchar, geom geometry);

-- Insert a 2500m radius polygon with center in given coordenates
insert into spots 
values (
        ST_SetSRID(ST_MakePoint(39.63467934415986, 2.6308822631835938), 4326),

I have several doubts about this code. Once the geometric figure is created with ST_Buffer, is it still preserving it's position? If I provide the SRID code with given lat-lon coordinates, do I need to use ST_SetSRID or it's completely unnecessary?

The thing is that I've discovered that using ST_Contains is always returning true, with no matter about the lat-lon values I'm providing to the function.


From postgis docs , ST_Buffer calculations are performed in the units of the SRID. In this case, you are using a buffer of 2500 degrees, so yeah... that covers any ST_Contains. Which also speaks to your question of importance of setting an SRID. It is important, and in your case you probably need a projected coordinate system such as UTM.

As to efficiency, and whether or not you even need to segment your circles (the last argument in ST_Buffer), that depends on how the GEOS library performs those calculations. I don't have any insight on that, and even if I did, you probably will just want to test segments vs. circles on a heap of points and measure the results.

In response to the question below, use ST_Transform after ST_SetSRID and before ST_Buffer to work in proper units. This will require you to also set and transform SRID of the comparison point. Here's an example that returns one point contained, and one point not:

ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(39.6346, 2.6308), 4326), 32632), 2500),
ST_Transform(ST_SetSRID(ST_MakePoint(39.6300, 2.6300), 4326), 32632)
) point1_is_in_buffer,
ST_Buffer(ST_Transform(ST_SetSRID(ST_MakePoint(39.6346, 2.6308), 4326), 32632), 2500),
ST_Transform(ST_SetSRID(ST_MakePoint(39.0000, 2.0000), 4326), 32632)
) point2_is_in_buffer;

Note that if you choose to store your Geometry in the projected coordinate system, you will be able to take advantage of spatial indexes using Gist. This can potentially reduce queries by orders of magnitude, especially on big cross joins.

  • Can ST_Buffer accept both meters as radius parameters and degrees in lat-long? If not, how can I transform degrees to meters in the radius?
    – amb
    Feb 14 '13 at 19:19
  • Between ST_Buffer and ST_SetSRID, nest one more function in there: ST_Transform. I'll come back later and roll an example into my answer.
    – Scro
    Feb 14 '13 at 19:28
  • Thanks for the answer. By creating polygons this way I'm using projected coordinate system? Also, it seems that ST_MakePoint needs parameters as lon-lat instead of lat-lon, as described here
    – amb
    Feb 15 '13 at 10:31
  • Yes, as long as the spatial meta data is correct. And the coord order is always x,y. Sorry if I goofed it.
    – Scro
    Feb 15 '13 at 16:20
  • I've been developing this and there is a problem when you set the SRID to UTM value 32632 because is location-dependant, and you should use one or another depending on the zone you're located. For solving my problem, I've found that is better to use ST_DWithin and just save in the database the point created using WGS84.
    – amb
    Apr 3 '13 at 13:33

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.