Access points of geometric path data in PostgreSQL 8.1

Question

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.

• Kassandry: 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?
• Craig Ringer: 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)
• Craig Ringer: 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.
• igal: @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: @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.

Answer 1

-- 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.