Go to content Go to navigation and search

Home

Current PostGIS Blog Articles


Search

Browse

RSS / Atom

Email me

textpattern

Creative Commons License
All Blog Articles, Data Models and Free Source Code by Simon Greener, The SpatialDB Advisor is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.

Loading Point Data from a CSV File in PostGIS

Friday February 20 2009 at 05:48

I recently wrote an article on the use of External File with Oracle tables to load point data into Oracle.

I thought I would have a look on how to do this in PostGIS.

A bit of research came up with the PostgreSQL COPY SQL command.

There appears to be no equivalent to Oracle’s Organisation External for a table. In PostgreSQL you simply create your table and then insert data into it from an external file. You don’t seem to need to concern yourself with special permissions to do this: just get in and do it.

Which is what we will do right now.

-- First off let's create our target table
DROP TABLE lidar;

Query returned successfully with no result in 15 ms.

CREATE TABLE lidar(
  easting float,
 northing float,
   height float
);

Query returned successfully with no result in 16 ms.

-- Now we can execute the COPY command to load our data (PostgreSQL or perhaps PGAdminIII) gets 
-- a little upset if we use Windows backslashes between the folders in the filename. 
-- Yes, you can use a double backslash which will work but PostgreSQL will continue to complain. 
-- Solution: simply use Linux/Unix forward slash filenames.
--
COPY lidar ( easting, northing, height )
    FROM 'c:/temp/lidar/ground.csv'
    WITH 
          DELIMITER AS ','
          CSV HEADER ;

Query returned successfully: 40401 rows affected, 297 ms execution time.

-- Now let's query the loaded data.
SELECT count(*)
  FROM lidar l
 WHERE l.easting  BETWEEN 478040  AND 478060
   AND l.northing BETWEEN 5228050 AND 5228070
   AND l.height   BETWEEN 200     AND 600;

count
bigint
------
301

-- Now let's add a 3D point geometry column to the table...
SELECT AddGeometryColumn('public', 'lidar', 'geom', 28355, 'POINT', 3);

addgeometrycolumn
text
-----------------
"public.lidar.geom SRID:28355 TYPE:POINT DIMS:3"

-- Now populate the added column
update lidar set geom = ST_SetSRID(ST_MakePoint(easting,northing,height),28355);

Query returned successfully: 40401 rows affected, 359 ms execution time.

-- Create a spatial index for faster querying
CREATE INDEX lidar_geom ON lidar USING GIST ( geom );

Query returned successfully with no result in 1344 ms.

-- Now, use the newly indexed spatial column in the spatial equivalent of the above query
SELECT count(*)
  FROM lidar
 WHERE geom && SetSRID('BOX3D(478040 5228050,478060 5228070)'::box3d,28355) 
   AND height BETWEEN 200 AND 600;

count
bigint
------
301

Now, that was pretty simple. There are certainly advantages to the PostgreSQL way of loading CSV organised data. I am not sure about the lack of security with regards to external file access though. One thing in Oracle’s favour is that externally organised tables can be used without having to load the data which can be useful in many situations.

Anyway, I hope this article is of use to someone.

Creative Commons License

post this at del.icio.uspost this at Diggpost this at Technoratipost this at Redditpost this at Farkpost this at Yahoo! my webpost this at Windows Livepost this at Google Bookmarkspost this to Twitter

Comment [4]

I haven’t played with copy much but this is what I recall. I’ll have to verify my assumptions.

I wouldn’t say there is a lack of security for PostgreSQL, but it is probably not as fine grained as Oracle.

1) As I recall, you can only copy from directories where the service account PostgreSQL is running under has rights to the folder. Since PostgreSQL should never run as root and even refuses to run under Administrator in windows — this is not so much of an issue.

It just so happens that the C:\Temp folder has security set to everyone.

2) Also have to verify, but I think only super admins in Postgres can use the copy command. I should know this, but I’m not sure if you can even assign copy rights to non-super users. So if you wanted to copy under another account, you would have to create a stored function that does the copy and set it to run as SECURITY DEFINER. Then it would run under the context of the owner of the stored function and if that stored function is owned by a super user then it would work.

Regina · 22 February 2009, 05:34 · #

Thanks a lot for that, it did help me a lot!

— Johannes · 4 December 2009, 14:19 · #

Thanks – very helpful. Note there have been some shifts in the COPY command’s syntax (though it should be backwards compatible). Also, if any of the field names or the table name begins with (possibly simply contain) a capital letter, strange effects can happen at the COPY step (e.g. errors to say that a Relation is not available (with the given table’s name).

— Jeremy Morley · 20 February 2012, 21:18 · #

I was worried, was solved in this article.
Thank you

From Japan

— nao · 6 April 2012, 02:49 · #