Npgsql parameterized query output incompatible with PostGIS

Question

I have this parameterized query in an Npgsqlcommand:

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

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

The query that is actually run against the DB looks like this:

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 help from Erwin Brandstetter here, it's apparent that the query needs to be simplified to work with PostGIS. He suggested this:

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

I guess I could create this with a dynamic query, where I manually update the query every time I run it, but is there a way to make this work with a Npgsql parameterized query?

Community · Accepted Answer · 2017-05-23 11:58:17Z

4

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 would hold this string:

POINT(96.6864379495382 32.792527154088)

.. pre-assembled from your other variables. Would result in 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)

Which should work.

If you have trouble assembling the string (like your comment demonstrates), there is a more elegant way. As per hint from @Paul on my previous answer - PostGIS provides a dedicated function for the purpose:

ST_MakePoint(double precision x, double precision y)

Details in the manual. With this, we finally arrive at:

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

Note the comma. Will it finally work now?
^{If not, just beat it with a sledgehammer. Grml.}

It does - with ST_SetSRID() now instead of ST_GeomFromText(). See comment.

edited May 23, 2017 at 11:58

CommunityBot

11 silver badge

answered Sep 14, 2012 at 18:45

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aren Cambre Over a year ago

Unfortunately, it still adds the types and escaping:

UPDATE raw.geocoding SET the_geom = ST_Transform(ST_GeomFromText('POINT(((E'-96.8569734692574 32.6630535721779')::varchar))', 4326),3081) WHERE id=((12490336)::int4)

, which causes a different syntax error: ERROR: 42601: syntax error at or near "32.6630535721779". My gut feeling is dynamically-constructed query.

Erwin Brandstetter Over a year ago

@ArenCambre: This thing won't be killed, huh? I added another one.

Aren Cambre Over a year ago

Thanks. That worked with an exception: ST_GeomFromText becomes ST_SetSRID. ST_GeomFromText is unnecessary because ST_MakePoint makes a point, so there's no text to transform. Instead, I just need to set the SRID of the point. Thank you for your help on this!

Erwin Brandstetter Over a year ago

@ArenCambre Makes sense. I amended the answer to a finally get to working parameterized query. Yay! :)

aloplop85 · Accepted Answer · 2013-01-04 13:11:08Z

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 with:

 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.

Collectives™ on Stack Overflow

Npgsql parameterized query output incompatible with PostGIS

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related