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

  • 2
    since Wkt is just regular text, so depending on the database you are using,probably nvarchar – elasticrash Jun 19 '15 at 6:13
  • Is this just a question about column type for storage of values that will not ever use more than one byte per character in UTF-8 (for which a CLOB [or maybe VARCHAR] would be adequate), or do you intend to make maps, index and perform spatial queries with these features (in which case GEOMETRY or GEOGRAPHY would be necessary)? Please edit the question to expand your problem statement to answer this, and to include the number of vertices, and a data sample from a few rows. (You could also correct the typo in the subject) – Vince Jun 19 '15 at 18:16

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
  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
-- 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)
  • Is it possible to add data by columns? Say I first input all the data in WKT colunm, then add the magnitudes and timestamp. Also if I have the WKT data already in LINESTRING(642044 4862161,642319 4862144) format, do I just simply input the data without any conversion? Still the format is geometry right? – LLLisa Jun 23 '15 at 19:04
  • You can always add and modify columns to a table. If the WKT is already properly formatted, then you can try to read that. The geometry can be created from many sources, such as WKT, WKB, GeoJSON, etc. – Mike T Jun 23 '15 at 20:02
  • How do I add only one column of data? INSERT INTO mydata(coords) VALUES ('(642044 4862161, 642319 4862144)'); can I do this? – LLLisa Jun 23 '15 at 21:35
  • INSERT works on rows, so you could insert one row with one column of data. But it would make more sense to insert a row of data with other attributes, like magnitude, etc. And if the row already exists, then an UPDATE statement is required. There are too many ways to get data in a table to describe. – Mike T Jun 23 '15 at 22:21

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.