Solar map: Difference between revisions

From Wildsong
Jump to navigationJump to search
Brian Wilson (talk | contribs)
Brian Wilson (talk | contribs)
mNo edit summary
 
(13 intermediate revisions by the same user not shown)
Line 1: Line 1:
== Overview ==
== Overview ==


This is about building a solar map for Benton county.
This page is about building a solar map for Benton county.


I got the idea after seeing an article in ArcNews about the [http://sf.solarmap.org/ San Francisco] and [http://solarmap.lacounty.gov/ Los Angeles] solar maps.
I got the idea after seeing an article in ArcNews about the [http://sf.solarmap.org/ San Francisco] and [http://solarmap.lacounty.gov/ Los Angeles] solar maps.


== Status ==
== Status ==
I just made this the "official" project of
the Corvallis Open Source GIS group. For more information, see the [http://groups.google.com/group/cvo-osgis?lnk=srg CVO-OSGIS] Google group and also [http://solarcreek.org SolarCREEK] which is where the map will eventually live. [[User:Brian Wilson|Brian Wilson]] 12:36, 18 April 2012 (MDT)


I just cooked up this idea 3 days ago. No screenshots available yet!
I just cooked up this idea 3 days ago. No screenshots available yet!
Line 26: Line 29:
=== Phase 3 ===
=== Phase 3 ===


Enhanced phase 2 by using Benton county LiDAR to identify roof top shapes.
Enhance phase 2 by using Benton county LiDAR to identify roof top shapes.
# Determine which ones are not shaded (by geographic features or bad slope)
# Determine which ones are not shaded (by geographic features or bad slope)
# Join the results with parcels to tie measurements to sites.
# Join the results with parcels to tie measurements to sites.


LA model looks at chimneys and nearby buildings and trees
LA model looks at chimneys and nearby buildings and trees
What about the City of Corvallis tree inventory???


== Software ==
== Software ==
Line 50: Line 55:
== Data ==
== 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.
I could use either OpenStreetMap or Google as a base layer, but that's so boring and I have up-to-date local data so at least for now I am using that.


=== Map layers ===
=== Map layers ===


I have access already to lots of Benton county data, will probably use that.
Currently all data are in the project used by the county,
 
NAD1983 HARN Oregon North (feet)
* 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?
| layer || source
|-
| roads || city
|-
| railroads || county
|-
| buildings || county
|-
| taxlots || county
|-
| addresses || AGI
|-
| parks || ? || poly
|-
| water || county || lines
|-
| water || ? || poly
|-
| hillshade || county
|-
| aerial photos || oregon explorer || WMS service
|}


=== Non-spatial data ===
=== Non-spatial data ===
Line 79: Line 92:
User accounts will include a list of properties using the taxlot id as the foreign key.
User accounts will include a list of properties using the taxlot id as the foreign key.


== Loading data ==
== Loading data into PostGIS ==


I have to write everything down since I will have forgotten everything by tomorrow...
I have to write everything down since I will have forgotten everything by tomorrow...
Line 117: Line 130:
  WHERE add.st_name LIKE 'ALEXANDER%' AND tl.pin = add.pin;
  WHERE add.st_name LIKE 'ALEXANDER%' AND tl.pin = add.pin;


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


My building
This is my house
  SELECT * FROM buildings WHERE gid=31875;
  SELECT * FROM buildings WHERE gid=31875;


Find what taxlot my building falls in.
Find the nearest schools
SELECT s.subname,s.situs, Round(ST_Distance(s.geom,b.geom)) AS distance
FROM schools as s, buildings as b
WHERE b.gid=31875 AND ST_DWithin(s.geom, b.geom, 10000)
      AND s.subname LIKE '%SCHOOL%' ORDER BY distance
ORDER BY distance;
 
Find what taxlot my house falls in.
  SELECT tl.owner FROM taxlots AS tl, bldg AS b  
  SELECT tl.owner FROM taxlots AS tl, bldg AS b  
  WHERE b.gid=31875 AND ST_Intersects(tl.geom, b.geom)='t';
  WHERE b.gid=31875 AND ST_Intersects(tl.geom, b.geom)='t';
Line 130: Line 152:
  WILSON BRIAN H
  WILSON BRIAN H
  (1 row)
  (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
Find all the buildings that are on taxlots that I own
Line 147: Line 174:


I am thinking it would be appropriate to make this a [[Solar CREEK]] project.
I am thinking it would be appropriate to make this a [[Solar CREEK]] project.
=== Needs ===
User accounts with different roles - someone who wants to report they have an installation and put it on the map needs a basic account. Someone who can manage the entire site needs an administrative account.
Support for the map
News or blog support?
Forum or discussion area?
=== Software ===


The server stack will include
The server stack will include
Line 152: Line 191:
* Python scripting (okay... maybe a little PHP too. No more perl though!)
* Python scripting (okay... maybe a little PHP too. No more perl though!)
* PostgreSQL / PostGIS database
* PostgreSQL / PostGIS database
* Geoserver on Tomcat application server
* [[Geoserver]] on Tomcat application server
* Apache web server serving Openlayers pages
* Apache web server serving [[OpenLayers]] pages
* Debian Linux
* Debian Linux


Geoserver will handle vector layers.
Geoserver will handle vector layers.


Apache will handle raster tiles.
Apache will handle raster tiles built with [[Mapnik]]


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


[[Category: Solar]]
[[Category: Solar]]
[[Category: GIS]]
[[Category: GIS]]

Latest revision as of 18:36, 18 April 2012

Overview

This page 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 made this the "official" project of the Corvallis Open Source GIS group. For more information, see the CVO-OSGIS Google group and also SolarCREEK which is where the map will eventually live. Brian Wilson 12:36, 18 April 2012 (MDT)

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

Enhance 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

What about the City of Corvallis tree inventory???

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 could use either OpenStreetMap or Google as a base layer, but that's so boring and I have up-to-date local data so at least for now I am using that.

Map layers

Currently all data are in the project used by the county, NAD1983 HARN Oregon North (feet)

layer source
roads city
railroads county
buildings county
taxlots county
addresses AGI
parks ? poly
water county lines
water ? poly
hillshade county
aerial photos oregon explorer WMS service

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 into PostGIS

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 the nearest schools

SELECT s.subname,s.situs, Round(ST_Distance(s.geom,b.geom)) AS distance
FROM schools as s, buildings as b 
WHERE b.gid=31875 AND ST_DWithin(s.geom, b.geom, 10000)
      AND s.subname LIKE '%SCHOOL%' ORDER BY distance
ORDER BY distance;

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.

Needs

User accounts with different roles - someone who wants to report they have an installation and put it on the map needs a basic account. Someone who can manage the entire site needs an administrative account.

Support for the map

News or blog support?

Forum or discussion area?

Software

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 built with Mapnik

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