[GIS] Using geometry or point for postgis

hibernate-spatiallatitude longitudepostgisst-geometry

I have a table with points that has lon/lat, i am adding postgis to my app. I am doing some tests and I found type POINT and type GEOMETRY. I want to convert my columns lon and lat to a point. For what i was reading it made more sense to add column of type POINT, but most examples I have seeing use geometry to store points.

@Column(columnDefinition="Point")
    private Point locacion;

Should I use geometry or geography instead of points. I am planning on using this points to find other points within a certain radius of meters

Best Answer

POINT is type of GEOMETRY though there is also a native POINT datatype in Postgres (Which as the name implies only deals with point features), I have mostly used Point as a geometry type in PostGIS.

There is also a data type of geography in PostGIS but it is limited to only geographic coordinate systems (According to the documentation it is restricted to WGS 84: SRID 4326, I haven't used it much myself so I'm not sure how it deals with spatial functions and queries) which means that you wont be able to use spatial queries in meters (which require a projected coordinate system)

Your best bet would be to import your GPX files into postgres using this guide, once you have your data in postgres you should be able to reproject it as you want and run spatial queries using meters.

PS: As Micah pointed out, using Geography datatype will return the results of the spatial queries you need in meters, however the Geography datatype is till limited to the lat/long based system so you need to be sure thats what your data uses.

Related Solutions

[GIS] Using PostGIS for a large trajectory dataset

About different geometry types: From your description it looks like you should absolutely store your trajectories as linestrings. If you store them as points or multipoints you will have to build linestrings in runtime if you don't only want to do the calculations on the points defining the trajectories but also what is between the points. an Example (in meter-based projection)

select ST_Distance('MULTIPOINT(10 10, 10 20)'::geometry,'POINT(0 15)'::geometry)

Will return: 11.1803398874989 which is the distance from the closest point in the multipoint to the point. while:

select ST_Distance('LINESTRING(10 10, 10 20)'::geometry,'POINT(0 15)'::geometry)

will return 10 which is the distance from the point to the closest point to the closest point on the line.

The same things about intersections test. If you only uses the points, a trajectory passing over a polygon without points in the polygon, will not be found if you only calculate on the vertex.points and don't use the points to define linestrings.

About projections you should absolutely transform (with ST_Transform) your data to a local projection if possible. It is faster and you will get a lot more functions to choose from.

About R you should be aware that postgreSQL have have a procedural language handling R directly in the database: PL/R

About indexing: The spatial index used in postgis is GIST which builds an index of the bounding boxes.

[GIS] PostGIS – Geometry or Geography find points within distance in meters

The definition of SRID 4326 is in fact IN DEGREES. ALso as it says in the ST_DWithin page:

Returns true if the geometries are within the specified distance of one another. For geometry units are in those of spatial reference and For geography units are in meters and measurement is defaulted to use_spheroid=true (measure around spheroid),

Since you're are using the 4326 SRID, you can cast from geometry to geography and vice versa like this :

select st_distance(<geom_column>::geography) from myTable;

Most of the functions in postgis are overloaded, meaning, they return values and run a different procedure based on the input data type.

eg:

boolean ST_DWithin(geometry g1, geometry g2, double precision distance_of_srid);

boolean ST_DWithin(geography gg1, geography gg2, double precision distance_meters);

they have the same name, but the first function returns T/F based on units of the srid, and the second returns T/F based on meters.

If you plug into the function geog types, for its calculation, will take into account the curve of the earth so the algorithm 'costs' a bit more.

If you want to check how much more it costs, you can run for each query an EXPLAIN-ANALYSE

Best Answer

Related Solutions

[GIS] Using PostGIS for a large trajectory dataset

[GIS] PostGIS – Geometry or Geography find points within distance in meters

Related Question