[GIS] PostGIS spacial data type to represent geo-located polygons

postgispostgresql

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 (
    'Test', 
    ST_Buffer(
        ST_SetSRID(ST_MakePoint(39.63467934415986, 2.6308822631835938), 4326),
        2500,
        4
    )
);

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.

Best Answer

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:

SELECT 
ST_Contains(
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_Contains(
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.

Related Question