3

I working on a GIS application which uses the PostgreSQL database with PostGIS extension. I already created the data access logic for entire project and everything works fine except the geometry value insert.

I have to pass geometry value as a function call:

INSERT INTO mygeotable (id, name, geom) VALUES 
    (1, "MyName", ST_GeomFromText('POINT(755888.4156 112458.556)', 23700))

As you can see I have to call the ST_GeomFromText PostGIS function on INSERT for the geom column value. This geom column is a string value from the view of ADO.NET but it can't be set as a text on INSERT.

I think ADO.NET automatically formats my INSERT command this way:

INSERT INTO mygeotable (id, name, geom) VALUES 
    (1, "MyName", "ST_GeomFromText('POINT(755888.4156 112458.556)', 23700)")

Which is wrong because of quotation marks!

How can I set INSERT command's geom parameter to use ST_GeomFromText function call?

I use parameters for every column. InsertCommand CommandText is: 

INSERT INTO mygeotable (id, name, geom) VALUES (id, name, geom)

The geom paramter DbType is String. There is no DbType for function call!

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
ggabor
  • 1,642
  • 4
  • 18
  • 23
  • I use parameters for every column. InsertCommand CommandText is: INSERT INTO mygeotable (id, name, geom) VALUES (id, name, geom). The geom paramter DbType is String. There is no DbType for function call! – ggabor Apr 15 '13 at 09:16

1 Answers1

1

Pass the parameters to the function as numbers in instead of passing the whole function call:

INSERT INTO mygeotable (id, name, geom) VALUES
    (id, name, ST_GeomFromText('POINT(X Y)', Z))

BTW double quotes like in "MyName" do not make valid strings as they are reserved for identifiers. Strings should be wrapped by single quotes.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • You right! The double quotes are invalid, I just typed here fast. So the problem is when I set value: row["geom"] = "ST_GeomFromText('POINT(1, 2)', 23700)" and then call adapter.Update(table) it throws an "invalid gemoetry" exception which means the insert command text was wrong, since from sql pane I can run the same query without error. – ggabor Apr 15 '13 at 13:23
  • If you want to pass numeric `X` and `Y` values to make a point, use `ST_MakePoint(X, Y)` instead. Also, you may need to wrap this in `ST_SetSRID(geom, 23700)` to set the SRID. [More info here](http://gis.stackexchange.com/questions/24486/how-to-insert-a-point-into-postgis/24487#24487) – Mike T Apr 15 '13 at 23:39