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.
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
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!
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
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
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
Smathermather's Weblog 