Category: SQL

Database, Database Optimization, PostGIS, SQL

PostGIS and LiDAR– oops!

I won’t offer much prelude.  Read this post, and this post first… . Inadvertently I demonstrated the value of spatial indices, i.e. I meant to use them and didn’t.  In attempting to sort my tables by their spatial index I got the following errors: ALTER TABLE lidar_ground CLUSTER ON lidar_ground_the_geom_idx; ERROR:  “lidar_ground_the_geom_idx” is not an index for table “lidar_ground” ALTER TABLE lidar_veg CLUSTER ON lidar_veg_the_geom_idx; … Continue reading PostGIS and LiDAR– oops!

Unknown's avatarsmathermatherLeave a comment
Database, Database Optimization, PostGIS, SQL

Rethinking PostGIS Analyses– Remembering to CLUSTER

Something’s been nagging at me as far as the level of optimization (or lack there-of) that I did to my database before my other posts of using PostGIS to analyze LiDAR data (e.g. this post).  It seemed my results were remarkably slow, but I couldn’t put my finger on why that was problematic. Then, as I was testing a smaller lidar dataset in order to … Continue reading Rethinking PostGIS Analyses– Remembering to CLUSTER

Unknown's avatarsmathermatherLeave a comment
Database, Database Optimization, PostGIS, SQL

PostGIS and LiDAR, the saga continues

I ran these tests a while back, but I’m just getting to post them now.  These were run on a late model MacBook Pro, with a 2.4GHz Intel Core 2 Duo, with a couple of gigs of RAM on PostgreSQL.  In Postgre, if I run SELECT version(); I get: PostgreSQL 8.4.1 on i386-apple-darwin10.0.0, compiled by GCC i686-apple-darwin10-gcc-4.0.1 (GCC) 4.0.1 (Apple Inc. build 5493), 64-bit My … Continue reading PostGIS and LiDAR, the saga continues

Unknown's avatarsmathermather4 Comments
Database, PostGIS, SQL

Clipping Data

I loaded a set of contours into the database that are really nice, up-to-date LiDAR and breakline derived contours.  The are engineering grade aerial contours, but they are a very big and complex dataset.  So I’ve done what I had hoped was the hard part, using shp2pgsql, I’ve converted them to PostGIS insert statements and dumped them into the database only to find that some … Continue reading Clipping Data

Unknown's avatarsmathermather2 Comments
Database, PostGIS, SQL

LiDAR processing and analysis in PostGIS (I hope…).

Alright, so let’s begin again with some LiDAR processing in PostGIS.  I really liked this discussion of multipoint conversions of LiDAR and other point datasets, but we won’t address making things more efficient here (yet).  Besides, these aren’t just points– we do want to retain their attribution… . Now, any regular database user will recognize my nubeness reading this post.  If you are that person, … Continue reading LiDAR processing and analysis in PostGIS (I hope…).

Unknown's avatarsmathermather20 Comments
Database, GDAL, PostGIS, SQL

GDAL Contours (cont.)

Well, I made some mistakes in the last post, not the least of which is I used the wrong flag for creating an attribute field with elevation.  What follows is a little more sophisticated.  It takes us from a series of DEM tiles from which I want 2-foot and 5-foot contours (using gdal_contour), and then dumps those shapefiles into PostgreSQL using shp2pgsql. First we prep … Continue reading GDAL Contours (cont.)

Unknown's avatarsmathermatherLeave a comment
Database, PostGIS, SQL

COPY command psql– loading large LiDAR Point Dataset

Ok, so the INSERT statements were too numerous for inputing the LiDAR point dataset (about a billion points… .)  They kept crashing the postgres daemon.  So, I used copy from a CSV file: COPY base.cuy_lidar_all FROM ‘c:/path/cuy_lidar_ground_veg.insert’ WITH CSV Keep your fingers crossed… . Continue reading COPY command psql– loading large LiDAR Point Dataset

Unknown's avatarsmathermather1 Comment
Database, Database Optimization, PostGIS, SQL

Limitations to Trigger Based Unique Constraint

A unique constraint implemented as a trigger checking hashed geometry seems like a good idea, that is until I applied it to a multi-10GB dataset.  Not surprisingly, it starts off fast on inserts, and slows down a lot as time goes on.  So, I thought I’d approach duplicates another way, by deleting them once they exist.  So for my table: base.cuy_contours_2 I have hashed my … Continue reading Limitations to Trigger Based Unique Constraint

Unknown's avatarsmathermather1 Comment
Database, PostGIS, SQL

Loading LiDAR data in PostGIS

For a variety of reasons, I want to load my whole LiDAR vegetation dataset into PostGIS.  I have it in raw, unclassified LAS files, and broken into classified, space delimited text files as well.  The unclassified LAS is nice, if I ever want to hone the data, but for now, we’ll assume the classified data are correctly classified by someone smarter than I am (a … Continue reading Loading LiDAR data in PostGIS

Unknown's avatarsmathermatherLeave a comment