5

I know there are similar questions on here that have been answered but unfortunately none of them work for me. I am hacking my way through creating a page which will display multiple locations on a map. I am using HTML 5 mapping plus Google maps. I have converted the addresses I have, into long/lat and they are stored in a CSV file along with other attributes.

I have tried storing as point, character varying and number as well as trying to create my own definition but have had no success. The table already exists and I just need to add these additional columns. I would rather not use PostGIS since that feels like adding in an additional level of complexity which I could well do without.

So what do you suggest?

  • 3
    I would say if you are doing a lot of GIS processing then adding PostGIS will actually remove some complexity. But even if you only use the PostGIS data types, I don't see how that adds "additional complexity". You install it once and then you are done. Btw: what exactly was the problem when using the point data type? – a_horse_with_no_name Jan 24 '15 at 12:30
9

The simplest solution without PostGIS would be to store lat/long as two number columns.
numeric for exact precision.
double precision or even just real if you don't need the precision.

I see no reason why the data type point shouldn't work as well. Per documentation:

Points are the fundamental two-dimensional building block for geometric types. Values of type point are specified using either of the following syntaxes:

( x , y )
  x , y

where x and y are the respective coordinates, as floating-point numbers.

Related:

1

I would rather not use PostGIS since that feels like adding in an additional level of complexity which I could well do without.

That's pretty silly. Firstly, latitude, and longitude without an SRS ID say nothing of where those points are. Are they project or unprojected? Where is POINT( 0 0 )? Are you addressing from East or West as negative?

Just use PostGIS. It's not complex. It does more of your problem so you don't have to.

CREATE TABLE foo ( geog geography(POINT, 4326) );
INSERT INTO foo (geog) ( ST_MakePoint(0,0) );

Comparison with geometric point

Even in the simple case,

  • a point in PostGIS can be exported to GeoJSON ST_AsGeoJSON, or a slew of export formats
  • can be aggregated into a GEOMETRYCOLLECTION
  • can be aggregated into a line that connects all points.
  • can be aggregated into a polygon (ST_ConvexHull)
  • can have the distance calculated between points using ST_Distance
  • can be reprojected to other reference systems
  • can be be indexed
    • is subject to KNN queries on index (find nearest point) with <=>
    • find all points-of-interest within X-range ST_DWithin

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.