Prezi

Present Remotely

Send the link below via email or IM

Copy

Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in the manual

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.

DeleteCancel

Make your likes visible on Facebook?

Connect your Facebook account to Prezi and let your likes appear on your timeline.
You can change this under Settings & Account at any time.

No, thanks

PostGIS and QGIS: Dynamic Layers with Views

No description
by Andrew Bell on 1 July 2014

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of PostGIS and QGIS: Dynamic Layers with Views

PostGIS & QGIS: Dynamic Layers with Views
Andrew Bell
What's going to happen?

The Story / Scenario
Live Demo in QGIS
How it works
Layers and SQL
Summary and possibilities

Geo-spatial consultant
QGIS and PostGIS Trainer
Project Manager
Astun Technology
Background in Local Goverment GIS



The Story
Identifying points of interest along a route
MSc
CREATE TABLE demo.myroutes
(
id serial NOT NULL,
geom geometry,
buffer integer
NOT NULL DEFAULT 20
,
my_interest integer,
)
My Routes Layer
CREATE VIEW demo.routebuffer AS

SELECT
myroutes.id,

st_buffer(myroutes.geom, myroutes.buffer) AS buffergeom
,
myroutes.my_interest

FROM demo.myroutes;

Route Buffer
CREATE VIEW demo.poi_near_route AS

SELECT
p.ogc_fid,
p.wkb_geometry
,
p.name,
p.classdesc,
p.weekdayopening,
p.weekendopening,
p.pointx_cla,
p.displayimage
FROM
demo.osmmpoi p,
demo.routebuffer rb
WHERE
st_intersects(rb.buffergeom, p.wkb_geometry)

AND

rb.my_interest = p.pointx_cla::integer
;

Locations inside buffer
CREATE OR REPLACE FUNCTION demo.tf_deleterecords()
RETURNS trigger AS
$BODY$

BEGIN

IF TG_OP = 'INSERT' THEN

EXECUTE 'DELETE FROM demo.myroutes' ;
return new;
ELSE RETURN NEW;
END IF;

END;
CREATE TRIGGER delete_records
BEFORE INSERT
ON demo.myroutes
FOR EACH ROW
EXECUTE PROCEDURE demo.tf_deleterecords();
Create Trigger / Trigger Function on My Routes
To delete the previous
route record
To Finish
Power-up QGIS with using PostGIS!

PostGIS Views & spatial functions enable virtual layers to dynamically return data on the fly.
No need to create shapefiles to buffer!
Enable non-techie GIS user to do cool and techie things.
QGIS is simple and effective

QGIS makes PostGIS queries accessible and visual

The technique uses:
PostGIS connectivity and layers
Data input Forms
HTML map tips
Key QGIS strengths
More possibilities:
Invalid geometries
Live tracking data
Emergency Planning
AREA AT RISK
Thank you

andrew@ogis.co.uk
andrewbell@astuntecnology.com

@therealandybell
Data download:

ftp://ftp.astuntechnology.com/qgisdemo/

QGIS dynamic layers - Andrew Bell.zip
demo.qgs
demo.backup
demo_queries.sql


See the full transcript