3

I am new to PostGIS. I have checked through a number of articles and posts on this, but still not finding a definite answer.

I am setting up a database for geo tracking purposes, which tracks assets (people, vehicles, equipment, etc). In two of the source systems, geo objects are defined as points, circles, polygons and rectangles.

One of my tables (a generic object table) will have a mix of all types. I can define the circles, rectangles and polygons as geometry(polygon, 4326) (for example), but what about the points?

Can I use geometry(geometry, 4326) for all geo types (polygons and points), or should I have 2 columns, one defined as geometry(polygon, 4326) and the other defined as geometry(point, 4326)?

To further complicate matters, future requirements could lead to linestrings being stored in the same table.

Two issues concern me: performance and future proofing the design.

Anyone with hard experience on this one?

  • Thank you for having taken the Tour where you will see that there should be only one question asked per question under our focussed Q&A format. – PolyGeo Feb 21 '18 at 5:04
  • It is generally a bad idea to mix geometry types in the same column. Index will be inefficient, you will often need to know the geometry type anyways to run spatial queries etc. It is a good practice to have separate tables for the various types (which you can then aggregate in a single view) – JGH Feb 21 '18 at 12:33
3

It's enough to create one column with the geometry type.

Like here:

CREATE TABLE TAB_SPATIAL
(
  gid Bigint serial
) WITHOUT OIDS;

select addgeometrycolumn('tab_spatial', 'the_geom', 4326, 'geometry',2);

Then you can add different geometries with:

INSERT INTO tab_spatial(the_geom)
VALUES(ST_GeomFromText('POINT(-71.060316 48.432044)', 4326));

INSERT INTO tab_spatial(the_geom)
VALUES(ST_GeomFromText('LINESTRING(0 0, 1 1, 2 1, 2 2)', 4326));

'POINT(-71.060316 48.432044)' is the WKT(well known text format) format to represent geometries

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.