TransWikia.com

PostgreSQL Query which returns geom column as WKT set in function

Geographic Information Systems Asked by user9491577 on September 23, 2020

Given: We are accessing a PostgreSQL DB from a VB NET application. When the query completes the DB rows are delivered in a VB NET DataTable. The Postgre DB geom column is in the format of System.Bytes[] (a byte array). I can convert the Byte array to the WKB format but converting the WBK to WKT format appears more complex. Hence, I was thinking I could return the geom col results as WKT.

My issue with this is I cannot use a SELECT * in the query to the DB. It seems I will need to parameterize ALL the columns in the SELECT command since I want the geom column in a DIFFERENT format.

Obvious Query:

SELECT * FROM temporary.summmarytable WHERE conditions = 'xxxxx'

has to become:

SELECT col1, col2 , col3. col4....ST_AsText(geom) WHERE condition = 'xxxxx'

I’m not a fan of long paramterized column lists as shown above. Is there a way to reset the geom output within a PostgreSQL function.

One Answer

Here is a solution suggestion:

   CREATE OR REPLACE _fn_querygeomWKT(tablename character varying DEFAULT 
   NULL::varchar, clause text DEFAULT NULL::varchar) 
   RETURNS TABLE (systemid varchar, city varchar, wkt text) AS 
   $BODY$
   DECLARE sql text DEFAULT NULL;
   BEGIN
   sql := 'SELECT system_id, city, ST_AsText(geom) WHERE myschema.' || tablename !! ' WHERE ' || clause;
   RETURN QUERY EXECUTE sql;
   END;$BODY$
   LANGUAGE plpgsql STABLE;
   ALTER FUNCTION _fn_querygeomWKT(character varying, text) Set search_path=public;
   ALTER FUNCTION _fn_querygeomWKT(character varying, text) owner to anowner;

The only issue with this solution I'm seeing is the lose of decimal precision. My polygon coordinates only show 2 decimal places. Any suggestions??

i.e. POLYGON((37.98 7.51, 38.51 7.51, 38.51 6.98, 37.98 6.98, 37.98 7.51))

Answered by user9491577 on September 23, 2020

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP