6

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

Improve this question
7
  • What do you mean by type GEOMETRY and type POINT? POINT is a type of geometry, your point dataset will always be along the lines of: geom geometry(Point,SRID)
    – Hasan Mustafa
    May 17, 2016 at 5:16
  • Ok, I might be wrong but when I look at the columns I see a Column of type point, and when I try to add a point i get this ERROR: column "locacion" is of type point but expression is of type geometry LINE 2: SET locacion=ST_SetSRID(ST_MakePoint(longitud, latitud), 432...
    – Juan Diego
    May 17, 2016 at 5:18
  • have you defined your SRID? and how are trying to add a point?
    – Hasan Mustafa
    May 17, 2016 at 5:20
  • I read some docs about this I am using hibernate, and It might be a hibernate thing. I am really new to postgis. I am trying to use hibernate spatial to manipulate this.
    – Juan Diego
    May 17, 2016 at 5:21
  • I have no experience with hibernate myself, what do you want to do exactly? Just add another point to your table?
    – Hasan Mustafa
    May 17, 2016 at 5:23

2 Answers 2

Reset to default
7

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.

Improve this answer
6
  • 1
    PostgreSQL does have native geometric datatypes and point is one of those. Read postgresql.org/docs/9.3/static/datatype-geometric.html. PostGIS adds new datatypes into PostgreSQL "GEOMETRY" and "GEOGRAPHY". Point as a geometry type of PostGIS geometry is not the same thing as the native PostgrSQL point datatype. GIS people tend to use PostGIS.
    – user30184
    May 17, 2016 at 6:32
  • I would point out the GEOGRAPHY data type is designed to work with Lon/Lat based data, but return spatial relations in meters. So the functions ST_Distance() and ST_DWithin(), when applied to features with a geography data type, give distances in meters, measured along the Ellipsoid. Similarly, St_Length(), and ST_Area() also return results in meters and sq. meters for Lon/Lat based geography data.
    – Micha
    May 17, 2016 at 6:43
  • @Micha personally I have never used GEOGRAPHY data type, I don't really know how it deals with the spatial relations, geometry seems a better option I think (more so as he is using GPX tracks and I'm still a bit doubtful about the srid in use, not really sure it's 4326)
    – Hasan Mustafa
    May 17, 2016 at 6:52
  • @user30184 I have read that and more on the data types and the differences between PostgreSQL and PostGIS, but I've tried to keep my answer as simple as possible since the OP is pretty new to GIS and postgres ( I dindn't want to go into too much detail to detract from the original purpose of the question )
    – Hasan Mustafa
    May 17, 2016 at 6:56
  • I think that it is too simplified to write First of all you should know that POINT is a type of GEOMETRY so it's not a case of using either one or the other. Juan had originally found the native PostgreSQL datatype "POINT".
    – user30184
    May 17, 2016 at 7:06
2

Besides what everyone else said, a Point data type works as a constraint. You can't insert lines in a field that's been defined as Point data type. But technically you can add lines, polygons and points in a field that's been defined as Geometry.

If you are sure your table has only points I would use Point data type, but just for the sake of concise definition. It should work fine either way.

Many examples and applications use Geometry (even when storing only points) just because some time ago Geometry was the only geometric type PostGIS incorporated.

Improve this answer
4
  • We are talking about the data type here. There is a data type Point, which is the equivalent of having a data type Geometry with a constraint on the dimension. Technically you can have a Geometry column without the Point constraint, that's why you CAN insert lines in a Geometry type field.
    – Marc Compte
    May 17, 2016 at 7:39
  • Who said anything about AddGeometryColumn? You can also ALTER TABLE foo ADD COLUMN pg_geom geometry;. So, technically, yes, you can.
    – Marc Compte
    May 17, 2016 at 7:50
  • I still don't know what's your point. There is a type Geometry and you MAY have this type accept any king of geometry. While having a data type Point will enforce there's only 0 dimensional elements. If your point is "one can have a Geometry type behave like a Point type" then yes, you may, but the type itself is not enforcing you. Is the AddGeometryColumn function which enforces the dimensional constraint, not the Geometry data type in any case.
    – Marc Compte
    May 17, 2016 at 8:12
  • Removed my comments which were mostly nonsense. Adding a link to this question instead stackoverflow.com/questions/1023229/spatial-data-in-postgresql.
    – user30184
    May 17, 2016 at 20:55

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.