[GIS] Adding a column of WKT format in the table (postgreSQL)

databasedatabase-designpostgresqlspatial-databasewell-known-text

I want to include this column of data that are in WKT format (they are mostly straight lines). What data type should I use?

I need to create this database for mapping. There are about 30000 features and their magnitude will be colour mapped and shown in 300 time intervals.

The database will be in this format:

magnitude; time; WKT coordinate

4; 2015-01-01; (642044 4862161, 642319 4862144)

I am wondering what column type should i input for the WKT column

Best Answer

You should add a PostGIS extension, and use the geometry type instead. It will handle 30000 features easily, and work with many GIS systems (e.g. QGIS). You can always extract the WKT equivalent from a geometry column using ST_AsText.

Here's an example workflow, assuming it's a table in postgres

--Say you have a table described in your question
CREATE TEMP TABLE mydata (
  gid serial primary key,
  magnitude numeric,
  time timestamp,
  coords text
);
INSERT INTO mydata(magnitude, time, coords)
VALUES (4, '2015-01-01', '(642044 4862161, 642319 4862144)');

-- Assuming you have PostGIS installed, spatially enable this database
CREATE EXTENSION postgis;
-- Add a geometry column with (e.g.) SRID=26910
ALTER TABLE mydata ADD COLUMN geom geometry(Linestring, 26910);
-- Convert the coordinate to WKT to load as a geometry
UPDATE mydata SET geom = ST_SetSRID('LINESTRING ' || coords, 26910);
-- Add a spatial index
CREATE INDEX mydata_geom_idx ON mydata USING gist (geom);

Now to query the table with proper WKT:

SELECT magnitude, time::date AS time, ST_AsText(geom) AS "WKT coordinate"
FROM mydata;

 magnitude |    time    |              WKT coordinate
-----------+------------+-------------------------------------------
         4 | 2015-01-01 | LINESTRING(642044 4862161,642319 4862144)
(1 row)
Related Question