Category: SQL

Database, GeoServer, OpenLayers, PostGIS, SQL

PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

GeoServer now has the ability to consume database views from PostGIS, not just raw tables. I say it “now” has that ability– I think that came online with GeoServer 2.x series, but I’m just “now” starting to take advantage of it. You can also create views on the fly within GeoServer, but I prefer to apply the logic at the database level, just in case … Continue reading PostgreSQL Views within GeoServer, GetFeatureInfo with Freemarker Templates, etc.

Unknown's avatarsmathermather5 Comments
Database, Database Optimization, LiDAR Shootout, PostGIS, SQL

Dialog– What qualifies as a benchmark? Part 1

Normally, my blog is a bit of a monologue.  It’s not a bad thing, but can be a little lonely.  Every now and then I get a great (and often doubtful) comments, which enhances things considerably. What follows is some dialog about the LiDAR shootout series, largely between Etienne and Pierre, posted with their permission: Pierre: “Etienne, Stephen, “I really appreciate the benchmark work you … Continue reading Dialog– What qualifies as a benchmark? Part 1

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

LiDAR Shootout! — New Chart, Final Results

In reviewing the final numbers and charts from Etienne and Pierre, above are the results we see.  The only revision is a moderate increase in speed for the PG Raster query. Final results in speed for lastools– ~350,000 points per second.  In other words– off-the-charts fast.  And the initial RMSE of ~25 feet was a mistake– it is probably closer to 0.2 feet. Stay tuned … Continue reading LiDAR Shootout! — New Chart, Final Results

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

LiDAR Shootout!

For a couple of months now I’ve been corresponding with Etienne Racine and Pierre Racine out of Montreal Laval University in Quebec City.  They decided to take on the problem of finding the speed and accuracy of a number of different techniques for extracting canopy height from LiDAR data.  They have been kind enough to allow me to post the results here.  This will be … Continue reading LiDAR Shootout!

Unknown's avatarsmathermather9 Comments
Database, PostGIS, SQL

Multi-Ring Buffers in PostGIS– Prep for Landuse Analysis

If we want to understand the factors affecting the quality of an ecological resource, adjacency is important. Adjacent land use and the fossil fuel inputs relative to keeping a land use in its current use seem to have strong correlation with, e.g. wetland quality.  I’ll have a deeper post on this with some citations, but this relatively simple process of analysis is called Landscape Development … Continue reading Multi-Ring Buffers in PostGIS– Prep for Landuse Analysis

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

Contours– Structuring PostGIS data for viewing with GeoServer

Naively structured data is my bane– the desire (and need) to get stuff done so often overtakes the time needed to do things the better way. So, we bootstrap. A long time ago, we managed to load in a few tens of gigs of contour data into PostGIS, partitioned it into 2ft, 10ft, 20ft, 50ft, 100ft and 250ft tables using select queries with a modulus … Continue reading Contours– Structuring PostGIS data for viewing with GeoServer

Unknown's avatarsmathermatherLeave a comment
Database, PostGIS, SQL

The PostGIS learning Curve: ST_Within and ST_Crosses

In an earlier post, I was frustrated with the intersection of a polygon and line datasets coming out as a geometry collection instead of a simple line dataset as expected. Nicklas Avén was kind enough to point out that this is a natural consequence of the logic of an intersection, the definition of ST_Intersects is: Returns TRUE if the Geometries/Geography “spatially intersect” – (share any … Continue reading The PostGIS learning Curve: ST_Within and ST_Crosses

Unknown's avatarsmathermatherLeave a comment
Database, PostGIS, SQL

Geometry Collections and Small Headaches

The disk that held the personal geodatabases of our contour datasets died a while back, but not before I loaded the contours into PostGIS and started serving them up. Our new intern is working on putting together some shapefiles layer groups in ArcGIS for map production, and asked for a missing one… . I don’t have it, but in principle can always extract it from … Continue reading Geometry Collections and Small Headaches

Unknown's avatarsmathermather5 Comments