Solar map: Difference between revisions

From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs)
Brian Wilson (talk | contribs)
Line 118: Line 118:


==== Useful queries ====
==== Useful queries ====
Something to look at http://casoilresource.lawr.ucdavis.edu/drupal/node/95


This is my house
This is my house

Revision as of 18:27, 6 January 2012

Overview

This is about building a solar map for Benton county.

I got the idea after seeing an article in ArcNews about the San Francisco and Los Angeles solar maps.

Status

I just cooked up this idea 3 days ago. No screenshots available yet! Brian Wilson 18:21, 4 January 2012 (MST)

Phase 1

  1. Create a web site with a dynamic browsable map of Benton county, Oregon
  2. Allow owners of solar installations to enter data about their sites.
  3. Put the sites on the map.
  4. Allow reports based on areas, for example, total power generated in Corvallis.

Phase 2

  1. Use building footprints to create rough estimates of available array size.
  2. Measure how many panels might fit.
  3. Create a database of the results
  4. Enhance the web site to allow searching the database by address.

Phase 3

Enhanced phase 2 by using Benton county LiDAR to identify roof top shapes.

  1. Determine which ones are not shaded (by geographic features or bad slope)
  2. Join the results with parcels to tie measurements to sites.

LA model looks at chimneys and nearby buildings and trees

Software

Everything for me is about PostGIS and Geoserver right now, this whole project is a proof-of-concept, learning experiment for me. In fact the only reason I started this page was to make a list of what I might want to import into PostGIS since that's about all I know how to do with it today.

QGIS for Ubuntu

PostGIS in a server is fine but on an Ubuntu client I want a current version of QGIS so I added the extra repositories and installed their version of QGIS https://launchpad.net/~ubuntugis/+archive/ubuntugis-unstable

The commands

sudo apt-add-repository ppa:ubuntugis/ubuntugis-unstable
sudo apt-get update
sudo apt-get upgrade
sudo apt-get dist-upgrade
sudo apt-get install qgis

Data

I don't think I have scaling issues, so I don't mind hosting the entire map. Otherwise I'd consider putting it on top of a Google map.

Map layers

I have access already to lots of Benton county data, will probably use that.

  • roads
  • railroads
  • building foot prints
  • taxlots
  • addresses
  • parks, city and county
  • Natural features including
  • elevations, probably in the form of hillshade
  • might want to show shading on the map for phase 2
  • aerial photos

I wonder if I want to include zoning information? What else?

Non-spatial data

The database of user accounts and the information they have submitted will be stored in PostgreSQL tables.

User accounts will include a list of properties using the taxlot id as the foreign key.

Loading data

I have to write everything down since I will have forgotten everything by tomorrow...

I am doing initial testing on Bellman at home, you can't see it unless you are on the Alsea Geospatial network because it's firewalled.

I am loading the data using techniques spelled out here Loading data into PostGIS.

I am using scripts, so that I can repeat the process endlessly until I get it right! The scripts are here bellman:/green/GISData/SolarMap

I will load data in its native projections into temporary workspaces, clean the data with SQL scripts, put them into the final projection and then copy it to the database that will be published.

Buildings

SELECT DISTINCT impr_type FROM buildings;
 impr_type   
--------------

STR-BLDG-OBS
STR-BLDG-UC
STR-DECK
STR-BLDG-RUI
STR-POOL
STR-CONCRETE
STR-BLDG-INT
STR-BLDG
STR-BLDG-GRE
STR-MISC
(11 rows)
CREATE VIEW bldg AS SELECT * FROM buildings WHERE impr_type = 'STR-BLDG';

Joining on pin looks wrong, add.st_name does not always match tl.situs

SELECT add.st_name, tl.situs, tl.owner 
FROM taxlots AS tl, address_nad_1983_harn AS add 
WHERE add.st_name LIKE 'ALEXANDER%' AND tl.pin = add.pin;

Useful queries

Something to look at http://casoilresource.lawr.ucdavis.edu/drupal/node/95

This is my house

SELECT * FROM buildings WHERE gid=31875;

Find what taxlot my house falls in.

SELECT tl.owner FROM taxlots AS tl, bldg AS b 
WHERE b.gid=31875 AND ST_Intersects(tl.geom, b.geom)='t';

    owner
----------------
WILSON BRIAN H
(1 row)

Find what city my taxlot falls in.

SELECT tl.owner, tl.situs, c.placename FROM taxlots AS tl, cities AS c 
WHERE tl.owner LIKE 'WILSON B%' AND ST_Intersects(tl.geom, c.geom)='t';

Find all the buildings that are on taxlots that I own

SELECT b.gid,b.impr_type,b.area FROM taxlots AS tl, buildings AS b
WHERE tl.owner='WILSON BRIAN H' AND ST_Intersects(tl.geom, b.geom)='t';
 gid  | impr_type |       area
-------+-----------+------------------
 6997 | STR-BLDG  | 280.284100081718
31875 | STR-BLDG  | 3114.20100001831
31881 | STR-DECK  | 430.011700273692
(3 rows)

Public web site

The final project will most likely be hosted in a cloud server or maybe at the Tektonic server I already pay money for to host HuPI.

I am thinking it would be appropriate to make this a Solar CREEK project.

The server stack will include

  • Python scripting (okay... maybe a little PHP too. No more perl though!)
  • PostgreSQL / PostGIS database
  • Geoserver on Tomcat application server
  • Apache web server serving OpenLayers pages
  • Debian Linux

Geoserver will handle vector layers.

Apache will handle raster tiles.

I wonder if I will use a framework such as GeoDjango?