Tag: Database

Database, Database Optimization, PostGIS, SQL

Trigger Speeeeed, or triggers and data maintenance in PostGIS

I have completed 4 out of 6 of my triggers for maintaining boundary data in PostGIS.  This is a follow-up to this post.  If you haven’t read my PostGIS trigger post, follow this link and come back.  I’ll wait.  Go on. Back?  OK.  So I’ve implemented my property boundary triggers.  I need about 6-7 different versions of our boundary for cartographic reasons– some which are … Continue reading Trigger Speeeeed, or triggers and data maintenance in PostGIS

Unknown's avatarsmathermather3 Comments
Database, Database Optimization, PostGIS, SQL

PostGIS and LiDAR, the saga completes?

I have to thank this post for the code that follows http://postgis.refractions.net/pipermail/postgis-users/2009-June/023660.html: I’ve been putting at this nearest neighbor for points problem for a few months now, never dedicating enough time to think in through, nor expending enough energy to be really great at SQL, let alone how it can be applied in spatial operations with an OGC compliant database like PostGIS.  But here’s my … Continue reading PostGIS and LiDAR, the saga completes?

Unknown's avatarsmathermather2 Comments
Database, Database Optimization, PostGIS, SQL

Optimizing PostGIS Geometry Functions– Mmm. Donut.

Well, how can I PostGIS without referring back to BostonGIS.  One day I’ll know… .  But first, the Simpsons:  “Mmm… … Donut.” A while back I read Regina Obe’s BostonGIS post on “Map Dicing,” essentially using a grid to dice up an existing dataset to a small granularity in order to take full advantage of spatial indexing, etc..  The post really intrigued me, and is … Continue reading Optimizing PostGIS Geometry Functions– Mmm. Donut.

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

Contour data and table management in PostGIS

My contour dataset is almost ready to go live.  Just a few more tweaks.  I was manipulating it in the public schema.  I really want it in my base map schema “base”. Want to change schemas on a table in Postgre? No problem: ALTER TABLE public.cuy_contours_2_clip SET SCHEMA base; Rename a table? No problem: ALTER TABLE base.cuy_contours_2_clip RENAME TO base.cuy_contours_2; Save space by not storing … Continue reading Contour data and table management in PostGIS

Unknown's avatarsmathermather1 Comment
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