A while back, I had the idea of creating a hash of geometry in order to create a unique constraint on geometry to avoid duplicate geometries in a PostGIS table. It works. It’s slow, but it works. I haven’t used it since. What I really wanted to do though was be able to detect if geometries were similar as well (not just detect identical geometries), … Continue reading PostgreSQL and Fuzzy Hashes– a natural combo?
This will be one of my shorter posts. pgadmin III allows for the development of scheduled tasks that run on your PostgreSQL database. As my PostGIS triggers have gotten more complicated, and since they run per transaction to keep ACID compliance, a big mess can be made by just updating a dataset (this is what vacuum is good for), and the time for inserts can … Continue reading pgagent — when triggers are too much
I maintain a lot of versions of our exterior property boundary layer for our park system for different purposes, e.g. this post. What I’d like to include in this automatically managed dataset is a multi-ring buffer version of the dataset for the purpose of a careful mask with fade (also useful for showing coastal vignettes— oops, ESRI link), e.g.: So, first we buffer: And discover … Continue reading Multi-ring buffers 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
An actual example of using the hashed geometry to uniquely constrain geometry (nothing really new here, just an example of using what we learned in the last post with a new problem): I’ve got a contours dataset from the county where I work that is in 20 different shapefiles ranging in size from 500MB to 2GB. I want to put them into a PostGIS database … Continue reading Unique constraint on geometry continued
In an earlier post, I griped about the limits to Postgres B-tree indexing, in that for large PostGIS geometries, I couldn’t create a unique constraint for PostGIS geometry. Abe Gillespie (blame me not him for any mistakes in what follows), suggested that I hash the geometry and create an index on that. In a follow-up e-mail, he suggested a series of tests in a trigger … Continue reading PostGIS Gripe—Limits to Postgre’s B-tree indexing—Followup
Yup, I’m going for hits with the above title. So, I’ve been playing with triggers in PostGIS (PostgreSQL) trying to streamline a process that I go through quarterly. We have a geographic boundary layer for our public entity: The green area is the area with public access. The purple without. I want to maintain a version of the boundary with this very simple distinction, and … Continue reading PostGIS Triggers
Smathermather's Weblog 