2

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?

4
  • 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
    Aug 25 '19 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? Aug 26 '19 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
    Aug 26 '19 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. Aug 26 '19 at 7:21
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.

3
  • Cool, so I suppose with javascript this wkx one should work?! Aug 27 '19 at 15:05
  • I didn't test, but it's possible yes, if you write your ewkb in hex Aug 27 '19 at 15:41
  • Will try and get back! Aug 27 '19 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.