Npgsql parameterized query output incompatible with PostGIS

Question

I have this parameterized query in the Npgsql command:

UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText('POINT(:longitude :latitude)', 4326),3081) WHERE id=:id 

:longutide and :latitude double , and id is int .

A query that actually starts from the database is as follows:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText('POINT(((E'-96.6864379495382')::float8) ((E'32.792527154088')::float8))', 4326),3081) WHERE id=((10793455)::int4) 

Thanks to the help of Erwin Brandstetter here , it is obvious that the request should be simplified to work with PostGIS. He suggested the following:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText( $$POINT(:longitude :latitude)$$::geometry, 4326), 3081) WHERE id = :id 

I think I could create this using a dynamic query, when I manually update the query every time I run it, but is there a way to do this work with a parameterized Npgsql query?

Answer 1

I am not an expert with npgsql , but I think your parameterized query could work like this:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText(:mygeom, 4326), 3081) WHERE id = :id 

And mygeom will contain this line:

 POINT(96.6864379495382 32.792527154088) 

.. preassembled from your other variables. The result will be a query like this:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText( (E'POINT(96.6864379495382 32.792527154088)')::text, 4326),3081) WHERE id=((10793455)::int4) 

What should work.

---

If you are having trouble assembling the string (as your comment shows), there is a more elegant way. As @Paul hinted at my previous answer , PostGIS provides a dedicated function for this purpose:

 ST_MakePoint(double precision x, double precision y) 

Details in the manual . In doing so, we finally achieve:

 UPDATE raw.geocoding SET the_geom = ST_Transform(ST_SetSRID( ST_MakePoint(:longitude, :latitude), 4326), 3081) WHERE id = :id 

Pay attention to the comma. Will he finally work now?
^{If not, just beat him with a sledgehammer. GRML}

Now it is ST_SetSRID() instead of ST_GeomFromText() . See Comment.

Answer 2

In my case, I used:

 NpgsqlCommand command = new NpgsqlCommand( "select ST_Distance( ST_SetSRID(" + "ST_MakePoint(@longitude, @latitude), 4326)," + "(select geom from segments where segment_id= @id )," + "true)", m_DBConnection); 

And it worked. Also try:

  NpgsqlCommand command = new NpgsqlCommand( "select ST_AsText( ST_ClosestPoint( ST_GeomFromText('POINT(" + longitude.ToString(CultureInfo.GetCultureInfo("en-US").NumberFormat) + " " + latitude.ToString(CultureInfo.GetCultureInfo("en-US").NumberFormat) + ")', 4326)," + "(select geom from segments where segment_id= @id )))", m_DBConnection); 

Thanks.