4

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?

2
  • 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
    Commented Feb 21, 2018 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
    Commented Feb 21, 2018 at 12:33

1 Answer 1

4

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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.