Top 5 Recent Articles
- Biography (1)
- Blog (27)
- Changes (1)
- Customers (1)
- Education (2)
- General Software (21)
- Image Catalog (2)
- Licensing (1)
- ManifoldGIS (3)
- MySQL Spatial (2)
- Networking and Routing (including Optimization) (3)
- Oracle Spatial (61)
- PostGIS (20)
- Press Releases (1)
- Source code (6)
- Space Curves (1)
- Spatial DB comparison (1)
- SQL (1)
- SQL Server Blog (17)
- SQL Server Spatial (General) (15)
- SQL Server Spatial (LRS) (37)
- Stored Procedure (2)
- XML (5)
Loading Point Data from a CSV File in PostGIS
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.