To import data into a PostgreSQL/PostGIS database I need this CSV format:

"1,nameOfPoint,0101000020E6100000689DA690152F114090338CFF9B0B4A40,2019-07-22 19:56:03.603+00,2019-07-22 19:56:04.243+00"

From my understanding the third field is a PostGIS geometry object (created inside PostGIS with ST_MakePoint). However I have coordinates. Can I convert coordinates into a PostGIS object outside of the database? Is there a conversion tool for this?

  • Postgres will try to cast the input strings using known conversions between TEXT and the column type; I can't test right now, but you can try to change the CSV to have a (E)WKT representation of your coordinates. Generally, if this is a recuring task, consider using a trigger. – ThingumaBob Aug 25 at 20:49
  • @ThingumaBob So when I convert my coordinates using (E)WKT it gives something like 0101000020E6100000689DA690152F114090338CFF9B0B4A40, right? And what kind of trigger do you mean? Within PostGIS? – musicformellons Aug 26 at 6:05
  • No, I mean to create a EWKT string, e.g SRID=<SRID>;POINT(<lon> <lat>); from your coordinates. I think PostGIS should be able to cast that to its geometry type on import (I assume you intend to use /COPY?). I would set up an ON INSERT trigger in PostgreSQL, though, yes. – ThingumaBob Aug 26 at 6:47
  • @ThingumaBob Ah... Well, basically I am on Google Cloud SQL (postgres) on which I can export a table and later import it again (no idea whether this uses /COPY under the hood...). Now I want to make such an import file myself (instead of from an export) to import it. – musicformellons Aug 26 at 7:21

The third field should be similar to what you can get with:

select encode(ST_AsEWKB(geom),'hex')

So it should be ewkb behind, displayed in hexadecimal. I find it not really easy to generate and pass it between computers, and especially to debug. You can find library to use it in different languages like python with plpygis

Personnally, I prefer to import it in text (ewkt typically) (I'm not sure about the ', maybe you don't need these):

"1,nameOfPoint,'POINT(2.3 3.4)',2019-07-22 19:56:03.603+00,2019-07-22 19:56:04.243+00"

And add a geometry column afterward with:

SELECT AddGeometryColumn ('my_schema','my_spatial_table','geom',4326,'POINT',2);
UPDATE my_spatial_table SET geom = ST_GeomFromEWKT(my_ewkt_field);

Note that if you have performance issues while doing so, it's quicker to create a new table instead updating one.

  • Cool, so I suppose with javascript this wkx one should work?! – musicformellons Aug 27 at 15:05
  • I didn't test, but it's possible yes, if you write your ewkb in hex – robin loche Aug 27 at 15:41
  • Will try and get back! – musicformellons Aug 27 at 16:15

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.