[GIS] Defining geometry types (point, polygon, geometry) in PostGIS

postgis

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?

Best Answer

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

Related Question