Tag: Postgre

Analysis, Cartography, Database, PostGIS, PostgreSQL, SQL, Trail Curation, Trails

PostGIS Cartographic Effects– Cartoonify Nearly Coincident Lines

I’m still working on this query, but I thought I’d post what I’ve done so far. My intent is to produce scale-dependent exaggeration of the distances between quasi-parallel lines. The reason for this is so that lines such as street lines which are nearly coincident at a particular viewing scale can be spread from each other, much in the same way great cartography lies a … Continue reading PostGIS Cartographic Effects– Cartoonify Nearly Coincident Lines

Unknown's avatarsmathermather1 Comment
CartoDB, Database, Leaflet, PostGIS, PostgreSQL, SQL

CartoDB, Leaflet, and a little anti-generalization

CartoDB is one of two hosted (read: cloud) PostGIS database implementations.  It has a maps API, an SQL API, and is some fun to use.  The other hosted PostGIS implementation is SpacialDB which has a Restful API, but can also take SQL.  I just got my key for the free version of that, so hopefully I will be reviewing use of that in the future … Continue reading CartoDB, Leaflet, and a little anti-generalization

Unknown's avatarsmathermather5 Comments
Database, PostGIS, SQL

PostGIS 2.0SVN and Raster

Got PostGIS 2.0 installed on a Debian machine, with Raster enabled, and put my first image in thanks to the directions in PostGIS In Action. Here’s the auto-generated SQL in pgAdmin: CREATE TABLE ldi_10ft ( rid serial NOT NULL, rast raster, CONSTRAINT ldi_10ft_pkey PRIMARY KEY ( rid ), CONSTRAINT enforce_srid_rast CHECK (st_srid( rast ) = 3734) ) WITH ( OIDS=FALSE ); CREATE INDEX ldi_10ft_rast_gis_idx ON … Continue reading PostGIS 2.0SVN and Raster

Unknown's avatarsmathermatherLeave a comment
Database, PostGIS, PostgreSQL

PostgreSQL dump for PostGIS upgrade

I’m working through the Hard Upgrade instructions for PostGIS 2.0SVN install instructions. No great surprises, but a succinct set of steps for trying out PostGIS 2.0 early. In our case, we needed to exclude larger (30GB) tables from the dump needed to do an upgrade– we’ll build those into the new database selectively. Looking to the pg_dump instructions, the dash “T” (-T) flag is what … Continue reading PostgreSQL dump for PostGIS upgrade

Unknown's avatarsmathermatherLeave a comment
Analysis, Database, Ecology, PostGIS, Security, SQL

Postgis for breakfast: ST_Donut — Revision

A commenter on my last ST_Donut post pointed out that we were essentially using not one but two buffers and and ST_Intersection to test where a point lay.  Bad Spatial SQL. Very bad Spatial SQL. Actually, at Paul Ramsey’s PostGIS for Power Users presentation at FOSS4G this year, I think he mentioned not doing that very thing, and I chuckled to myself, “Well, even I … Continue reading Postgis for breakfast: ST_Donut — Revision

Unknown's avatarsmathermatherLeave a comment
Analysis, GeoServer, PostGIS

What is the center line of a complex polygon? (cont. 3)

Just a quick post on this one, this time. I haven’t implemented an approximation of Bálint Miklós‘ Scale Axis Transform in PostGIS yet, and I don’t think I’ll dare try in GeoTools for GeoServer just yet, but I thought I’d give a preview of the sensitivity of the medial axis calculations in “bumpy” streams with the following image: As you can see, the problem isn’t … Continue reading What is the center line of a complex polygon? (cont. 3)

Unknown's avatarsmathermatherLeave a comment
Analysis, Database, Ecology, PostGIS, Security, SQL

Postgis for breakfast: ST_Donut

This post typed into my iPod as an homage. Assisted today by my collegue, J. Stein. Moderate obfuscation of locations is an important technique for the protection of data, say something sensitive like the nesting locations of the very rare and strange fuzzy-bellied gnat catcher. We still want to display the data, but want to make it slightly wrong. A naive approach would place it … Continue reading Postgis for breakfast: ST_Donut

Unknown's avatarsmathermather3 Comments
Database, PostGIS, SQL

Cleaning PostGIS Geometries — Revised

In a previous post: “Another short post. Got self intersecting geometries? Want to fix them inside PostGIS? Me too. Fortunately, someone has already written the code, so you and I don’t have to: http://linfiniti.com/2010/07/cleaning-geometries-inside-postgis/ Now, let’s see how long it takes to run on my 760,000 record landcover table… .”   As usual, there’s a better way.  With PostGIS 2.0, you can (will be able … Continue reading Cleaning PostGIS Geometries — Revised

Unknown's avatarsmathermatherLeave a comment
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