SELECT b.gid AS gid,
ST_Closestpoint(ST_Collect(a.the_geom), b.the_geom) AS the_geom,
b.stream_nam
FROM public.hydro_polyline_merge1 a INNER JOIN base.streams_phwh b
ON ST_Dwithin(a.the_geom, b.the_geom, 1000)
GROUP BY b.gid, b.the_geom, b.stream_nam Continue reading SQL/MMore Acrobatics
Another short post… . I have in place a framework for maintenance of our boundary data that works really well. I feed an edited shapefile into the database, and PostGIS creates all the versions of the boundary that need created. The database then exports the managed data to shapefile so there is a database version and a shapefile version depending on need. When all was … Continue reading Shapfile spatial indices and PostGIS dumps
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
A quick review of costs to search with increasing distances. Reference this original post for the code being run. SELECT DISTINCT ON(g1.gid) g1.gid as gid, g2.gid as gid_ground, g1.x as x, g1.y as y, g2.z as z, g1.z – g2.z as height, g1.the_geom as geometry FROM veg As g1, ground As g2 WHERE g1.gid <> g2.gid AND ST_DWithin(g1.the_geom, g2.the_geom, 3.5) ORDER BY g1.gid, … Continue reading Cost of nearest neighbor search depending on distance
There’s much to be said for knowing your data in order to best optimize the analysis of it. Beyond all other bits of cleverness, having a functional understanding of your problem is the first step toward conceiving an intelligent and efficient solution. One thing that I didn’t do two posts ago was to spend any time deciding how far out the search for nearby points … Continue reading Further optimization of the PostGIS LiDAR Vegetation Height Query
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
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?
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
Smathermather's Weblog 