3

Summary:

I would like to perform basic operations on the points of geometric path data contained in a PostgreSQL 8.1 server which I do not have administrative access to. In particular I would like to be able to iterate over these points using SQL and to access paths as arrays (or something functionally equivalent).


Background:

I'm a novice database user who is working with an unsupported legacy application which is using PostgreSQL 8.1 as a back-end database. I would like to be able to manually run some sanity checks on the data in the database in pure SQL (or PL/pgSQL), i.e. I'd like to be able to avoid using an external programming language, which is what I'm currently doing.

Several tables in the database include columns containing geometric data. Here is a description of one such table:

psql -U "${appuser}" -d "${appdatabase}" -c '\d+ path_table'

            Table "public.path_table"
 Column |  Type   | Modifiers | Description 
--------+---------+-----------+-------------
 id     | integer | not null  | 
 path   | path    | not null  | 
Indexes:
    "path_table_pkey" PRIMARY KEY, btree (id)
Check constraints:
    "path_table_id_check" CHECK (id > 0)
Has OIDs: no

I consulted the section on Geometric Functions and Operators from the PostgreSQL 8.1 Manual, but to no avail.

Since this might be relevant, I'll mention that I believe that PostGIS 1.3.6 is installed, i.e.:

$ psql -U "${appuser}" -d "${appdatabase}" -c 'SELECT PostGIS_full_version();'

                                  postgis_full_version                                  
----------------------------------------------------------------------------------------
 POSTGIS="1.3.6" GEOS="3.1.1-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" USE_STATS
(1 row)

My understanding is that I can get a list of all installed functions by using the psql \df meta-command, i.e.

$ psql -U "${appuser}" -d "${appdatabase}" -c '\df'

My understanding is also that all of the available PostGIS commands should be prefixed by the string 'ST_' (denoting spatial type), so that the following should produce a list of all the available PostGIS commands:

$ psql -U "${appuser}" -d "${appdatabase}" -Alt -c '\df' | cut -d'|' -f2 | grep -i '^st_'

Inspecting this list didn't produce any likely candidates besides the ST_Dump function, which doesn't appear to apply to a path type:

SELECT st_dump(path_column) FROM table;

ERROR:  function st_dump(path) does not exist
HINT:  No function matches the given name and argument types. You may need to add explicit type casts.

Apparently the st_dump argument needs to be of type geometry, so I tried following the hint and casting the path to a geometry type:

SELECT st_dump(path_column::geometry) FROM table;

ERROR:  cannot cast type path to geometry

I also consulted the Geometry Accessors of the PostGIS 1.3.6 Manual but apparently I don't know enough to be able to cull a solution from the documentation.

8
  • Can you show us the full definition of your table, please? And to fully understand, you want the WKT (Well-known text) of the data, for verification purposes?
    – Kassandry
    Nov 6, 2015 at 20:46
  • 1
    8.1? Seriously? That's prehistoric. Anyway, maybe PostGIS was installed in a different schema that isn't on the search_path? Try \df *.ST*? (Assuming that works in 8.1) Nov 7, 2015 at 7:32
  • Also, path is a built-in PostgreSQL data type, at least in current versions. Not a PostGIS type. I think PostGIS provides functions to convert path to geometry and otherwise work with it; maybe a look at the manual for your version will be informative. Nov 7, 2015 at 7:36
  • @Kassandry Thank you for your response. I added a table description per your request; please let me know if that's not what you were looking for. I'm not sure how to get the WKT – the st_astext function is producing an error.
    – igal
    Nov 7, 2015 at 8:48
  • @CraigRinger Unfortunately I'm completely serious. Your suggested command works, but I'm not sure what the purpose is – are you suggesting that another version of PostGIS might be installed somewhere? I apologize if my wording seemed to suggest that the path datatype was not a native type; I only mentioned PostGIS because I thought it might be relevant to the situation. Thank you for your help.
    – igal
    Nov 7, 2015 at 8:52

1 Answer 1

0
-- Create a temporary table to store the extracted points
CREATE TEMP TABLE extracted_points (id INTEGER, point TEXT);

-- Loop through each row in the path_table
DO $$ 
DECLARE
  path_row RECORD;
BEGIN
  FOR path_row IN (SELECT id, path FROM path_table) LOOP
    -- Convert the path to a text representation
    EXECUTE 'INSERT INTO extracted_points (id, point) VALUES ($1, $2)' USING path_row.id, ST_AsText(path_row.path);
  END LOOP;
END $$;

-- Now, the extracted_points table contains the path id and its points as text
SELECT * FROM extracted_points;


Please note that the above code is not very efficient since it loops through each row of path_table. In newer versions of PostgreSQL and PostGIS, there are better ways to handle this, but with PostgreSQL 8.1 and PostGIS 1.3.6, this is a workaround to achieve your goal of extracting points using SQL only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.