Search code examples
c#postgresqlpostgisnpgsql

How to apply ST_GeomFromText() function to format text when using PostgreSQLCopyHelper (https://github.com/PostgreSQLCopyHelper/PostgreSQLCopyHelper)

INSERT INTO geotable (geometrydata) values (ST_GeomFromGeoJSON(@geometrydata));

How can I run the above insert statement to posgresql database using PostgreSQLCopyHelper in c# code. Tried to do something like this.

var copyHelper = new PostgreSQLCopyHelper<GeoData>("geotable ") .Map("geometrydata", x => x.geometrydata, NpgsqlDbType.Geometry);

geometrydata is a string like below.

`var geometrydata = "{\"type\":\"Polygon\",\"coordinates\":[[[-81.800007507357,24.491670214271,0.0],[-81.800007507357,24.500003548802,0.0],[-81.7916741718259,24.500003548802,0.0],[-81.7916741718259,24.491670214271,0.0],[-81.800007507357,24.491670214271,0.0]]]}"`

How can I format data using postgis function when using PostgreSQLCopyHelper. (https://github.com/PostgreSQLCopyHelper/PostgreSQLCopyHelper )

Solution

  • You cannot call functions while loading data with COPY.

    If you really need that, you would have to create a view on the table and use an INSTEAD OF trigger on the view that inserts a suitable processed row into the table.

    It will probably perform better to load the data to a staging table and perform the conversion with an UPDATE on all rows.