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.
    – geozelot
    Commented Aug 25, 2019 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? Commented Aug 26, 2019 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.
    – geozelot
    Commented Aug 26, 2019 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. Commented Aug 26, 2019 at 7:21

1 Answer 1


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?! Commented Aug 27, 2019 at 15:05
  • I didn't test, but it's possible yes, if you write your ewkb in hex Commented Aug 27, 2019 at 15:41
  • Will try and get back! Commented Aug 27, 2019 at 16:15

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.