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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP