GeoServer Layers

[Keith Bornhorst]

Is there any guidance available on what GeoServer layers are required for OTM and how each layer should be set up? Different OTM implementations seem to have different layers:

Asheville's GeoServer Layer Preview - two layers, both using tampa_trees; custom workspace

Philadelphia's GeoServer Layer Preview - lots of layers; tampa_trees, tampa_neighborhoods, search?, plots?; cite workspace

Without the map working I haven't bothered creating layers in GeoServer, but I'll most likely need some guidance when I start.

What are the required layers, the tables they reference and any special settings needed?

Tanks,
Keith

[Andrew Thompson]

Hi Keith,

Some of the geoserver settings you'll need to set are in the local_settings.py file, and to that extent the names of layers can be what you want. See the tail end of: https://github.com/azavea/OpenTreeMap/wiki/External-Data

Hope that helps when you get to it,

Andrew

--
You received this message because you are subscribed to the Google Groups "opentreemap-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to opentreemap-us...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

Andrew Thompson, Community Evangelist

Azavea |  340 N 12th St, Ste 402, Philadelphia, PA
atho...@azavea.com  | T 215.701.7689  | F 215.925.2663
Web azavea.com  |  Blog azavea.com/blogs  | Twitter @azavea

Join our community! Google Groups:

Cicero API | OpenTreeMap | GeoTrellis | OpenDataCatalog

[Keith Bornhorst]

Thanks for the pointer, Andrew, but I was hoping that something more detailed (for the non-GIS Analysts) was available.

GeoServer is up and running and I've figured out what Workspaces and Stores are. Looking at phillytreemap.org/geoserver/web/ it looks like I need to publish layers for treemap_neighborhood, treemap_tree and maybe treemap_plot.

I'll give it a shot, but there's 4 tabs of fields to enter.... not sure what Native/Declared SRS to choose, etc.

treemap_tree has nothing in it so I went with EPSG:4326 and told it to figure out the bounding boxes.

treemap_neighborhood has a single neighborhood in it and EPSG:4326 was auto-selected for both Native/Declared. Clicking "Compute from [data / native bounds]" gave an error:

java.lang.RuntimeException: java.io.IOException: Error occured calculating bounds 

...

Caused by: java.io.IOException: Error occured calculating bounds

Caused by: org.postgresql.util.PSQLException: ERROR: permission denied for relation treemap_neighborhood

using (-1, -1, 0, 0) and (0, 0, -1, -1) got it to save, but there's still three other tabs (Publishing, Dimensions, Tile Caching) that I guess I need to figure out...

[Justin Walgran]

Keith,

Based on the "permission denied" error message I suspect that the account you are using in the GeoServer "Store" cobfig does not have read access to all the PostGIS tables. If you are running Postgres 9.x you could use a query like the one below to make sure that the account GeoServer is using to connect can read all the tables:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO {user_set_in_geoserver_store_config};

-Justin

--

[Keith Bornhorst]

That was exactly the problem, Justin, thank you.

[Bob Troester]

Keith,
Continuing your explorations of the 7 "known" OTM installations (http://www.azavea.com/products/opentreemap), I looked for exposed geoserver installations like the one for Philadelphia that you viewed: phillytreemap.org/geoserver/web/. It turned out that Asheville and SanDiego use a different url structure (or a different server for GeoServer) and a search of them resulted in a 404 page not found.

Looking at the other five, I found all defined a layer evidently built on the treemap_neighborhood table, 4 used treemap_plot, all define a treemap layer group consisting, evidently of treemap_neighborhood and either treemap_plot or some view of it, like 'present_plots' or 'plots_with_trees'. All had a layer called 'tree_search', evidently based on a general search view. Grand Rapids was a little different, having a 'gr_canopy' layer. The British Treezilla, the latest installation, was simplest, with treemap_neighborhood, treemap_plot, tree_search, and a layer group, tz.

Only PhillyTreeMap had a layer named treemap_tree, which I would guess is an unused leftover. Why? Because table treemap_tree has no geoextension, it is joined to table treemap_plot that does. So given treemap_tree by itself, GeoServer can't serve anything.

You can see the tables that might be referenced by GeoServer by executing the following SQL either in psql or pgadmin3 (if you are using PostgreSQL, of course!)

    select table_name, column_name from information_schema.columns where udt_name = 'geometry';

resulting in a list of tables (where each entry should be preceeded by 'treemap_') of neighborhood, supervisordistrict, zipcode, exclusionmask, geocodecache (2 fields), plot_audit, plot, and plotpending. But no treemap_tree table! (For a join of tree and plot tables to combine tree characteristics with geometry, see the very end of the treemap/search.py code.)

(You can actually see this list of mapable tables much quicker simply by looking with pgadmin3 at the 'geometry_columns' table that includes all of the above, along with a couple of 'tmp' entries.)

Sorry for being so verbose, but I'm working all this out for myself as I go, and I thought there might be others in the same state of befuddlement!
Bob

[Lee]

Since I noticed "Grand Rapids" showed up, I suppose it's time to make my entrance. I'm the program coordinator at Friends of Grand Rapids Parks, who manages the Grand Rapids Urban Forestry Project, including the tree map. While we did not have a hand in setting up the tree map ourselves, I'm always happy to answer any questions around our map, its use, and any of the finished product issues we may or may not have run into.

More than anything, an FYI that GR is here, and willing to converse.

--
All the best,
Lee
ISA Certified Arborist MI-4148A
Registered Forester #46043

--

[Bob Troester]

Lee,
Since everyone else seems to be on Easter break, let me take it upon myself, as (hopefully) a soon-to-be fellow user of OTM (if I can ever get all the blasted parts to work together), to welcome you to the forum.

I mentioned Geat Rapids in a previous post because I noticed your GeoServer site (treemap.urbanforestproject.com/geoserver/web/) shows you have a special "canopy" layer for your map that other installations aren't using. (I was especially interested since I was born in Detroit and have relatives near GR.) Then I googled some news about your project (http://www.rapidgrowthmedia.com/features/07262012trees.aspx) about your goal to increase the canopy from 30 percent now to 40 percent by 2020 so I can see the reason for the addition. It looks like a great idea - where do you get the data? I'm vaguely aware of various canopy studies (I believe our own University of Vermont has researchers in this field) but is such info readily available? I guess it wouldn't have to be updated but once every few years, but it might help especially to fill in a map until individual trees are plotted.

Here in tiny Montpelier, Vt we're surrounded by mostly forested land but have only an estimated 5,000 street trees (2,000 mapped), which are the major focus of our volunteer tree board and our tree warden/parks director. It made my jaw drop to read that GR is currently spending $200,000 planting trees - good for you! (our tree board budget is 1% of that figure!).

So, another question: after a year with OTM now, what kind of an increase are you seeing in citizen involvement and interest in city trees?
Bob

[Bob Troester]

Sorry, my finger slipped: *Grand* Rapids, of course!

[Lee]

Bob,

Thank you for your questions.

Our tree canopy layer was based on several Urban Tree Canopy studies we've had done over the last few years. Grand Valley State University has researchers with similar backgrounds to those you're mentioning in Vermont. We've used available Michigan DNR grants to help fund these efforts. All in all, it was part of the City's early efforts to gain more information on its Urban Forest leading up to the adoption of the 40% canopy goal. In short, it's data based on an analyses you have to have done.

In regards to the canopy layer, I have mixed views. On one hand, it's useful to locate trees which haven't been mapped. If there's a green "splotch" but no green "dots", it probably means there's mapping to be done. On the other hand, it sort of makes using the aerial view and finding locations a bit difficult. The canopy layer doesn't disappear until a pretty close zoom.

As far as Citizen Involvement is concerned, our tree map was launched in August and began with some excitement and involvement. We were lucky enough to have nearly 17,000 trees that were previously mapped as part of other projects to "start" our map with. Since then, we've hosted a series of mapping workshops, mapathons, mapping parties, and various social education stunts that utilize the tree map. Additionally, we're using the map to recognize tree planting sponsors or specific types of trees. For example, we use the tree map to help nominate this year's mayors tree of the year.

Public involvement has been all but nil during the winter. Understandably, it's harder to identify trees. That said, we're kicking off our Citizen Forester program this spring, and I'm expecting far more "useful" engagement opportunities and educated citizens to coalesce around our project and subsequently the tree map due to our increase in programming opportunities. On another note, I'm working to establish a pilot area in hopes of encouraging a manageable citizen-led inventory effort. Perhaps our downtown?

All in all, we're still working on developing interest around the tree map. We've found the best promotional results through events like Mayor's tree of the year or localized mapathons. After we've gotten a little further in our Citizen Forester program, I'm hoping to see more organic increases in map usage. Generally, I think continued programming is key to promoting tree map usage.

Hope this helps.

P.S. I've been here about 10 months. Previously, I worked for a similar organization in Detroit. We have similar roots.

--
All the best,
Lee
ISA Certified Arborist MI-4148A
Registered Forester #46043

[Bob Troester]

Lee, I like that Mayor's Tree of the Year - I'm pretty sure our mayor would be game for it - maybe, since we're the state capital, the governor! Also, we could enlist the school kids - there's one who's come on a couple of work who's a wiz at identifying trees.
Bob

[Kurt]

Here's what I've got about setting up GeoServer, so far. I don't have it working in the map window yet but I am getting a good layer preview from the GeoServer app.
I'm putting together a "tutorial" on the steps to getting GeoServer and TileCache working with Google Maps (or Bing?), as overlays. Here's what I've got so far--not much but just starting to document it. Anything anyone can add will be greatly appreciated.

Install GeoServer for OpenTreeMap

Install Tomcat7:

     sudo apt-get install tomcat7

Setup an external data folder for Tomcat7:

I chose to move the data folder out of the main GeoServer directory because I have installed, wiped and reinstalled server configurations a few times, and lost my data. Also, this is recommended on the GeoServer website, to avoid blowing away your data if you choose to install a GeoServer update.

I used the WAR file installation method so the data directory is located at:  

/var/lib/tomcat7/webapps/geoserver/data/

I moved it to: /var/lib/geoserver_data/.

1) Create your new directory and give Tomcat7 ownership of it:

sudo mkdir /var/lib/geoserver_data

sudo chown tomcat7:tomcat7 /var/lib/geoserver_data

2) Modify the web.xml file in /var/lib/tomcat7/webapps/geoserver/WEB-INF/

at about line 50 look for the GEOSERVER_DATA_DIR parameter declaration. It will be commented out. Uncomment the block and enter the new directory path between the param-value tags.

<context-param>

     <param-name>GEOSERVER_DATA_DIR</param-name>

     <param-value>/var/lib/geoserver_data</param-value>

</context-param>

There are instructions and other options at the following link:

http://docs.geoserver.org/latest/en/user/datadirectory/index.html

Change Tomcat port to 8081:

Edit the /etc/tomcat7/server.xml file.

At about line 72 change:

     <Connector port="8080" protocol="HTTP/1.1"

to:

     <Connector port="8081" protocol="HTTP/1.1"

The Shape FILES:

GeoServer needs to have data to render the various boundaries of your geographic area as overlays for a Bing or Google map. I am working on the state of Utah so I got some students in the geography/cartography/geo-location fields here, at Utah State University to prepare a set of shape files for import into the OTM treemap_neighborhood table. They generated a municipalities file, with all the cities, a counties file, and a file with the state boundary.

The real work they did was converting existing data into a format that was most common for all the parts of OTM to use. EPSG:4326 seemed to be the most commonly mentioned format on the websites I researched, (it’s compatible with wgs84, of which I know nothing about, but the two are rumored to be intimately connected and have been seen together frequenting Cuban coffee shops and various seedy bars and restaurants in the downtown area late at night).

The “shape files” actually consist of a set of files; .dbf, .shp, .prj, .sbx, .shx, and maybe others. You may not have all of these. My understanding is, the most important for OTM use are the .dbf files and maybe the .shp file, but not required if you upload the data to the treemap_neighborhood table (GeoServer can render layers from .shp files if you don’t put the data in the database).

Create your database:

I’m assuming most of us have already done this as part of the basic installation instructions. The part that is most important, relating to GeoServer is the template_postgis .

sudo -u postgres createdb -T template_postgis YOUROTMDATABASENAMEHERE

 

Add shape file data to your database:

To start, I didn’t try to get the data from the shape file(s) directly into the treemap_neighborhood table. I took it in a few steps. 1) Create the .sql files. 2) Create new tables in my OTM database with the .sql files. 3) Move columns from new tables into the treemap_neighborhood table as needed.

1. Create your .sql files from the shape .dbf files:

shp2pgsql -s 4326 YOURFILENAMEHERE > YOURFILENAMEHERE.sql

2. Insert your .sql file data into a new table:

sudo -u postgres psql YOUROTMDATABASENAMEHERE -f YOURFILENAMEHERE.sql

 

3. There are a few things to keep in mind:

a) You're going to need a little SQL to move data around. Not much just basic INSERT, SELECT, SET, etc. Familiarity with Postgres and pgAdmin3 will make this go faster.

b) All fields in the treemap_neighborhood table are required and cannot have null values. The “character variable” data type (or varchar for the MySQL fans out there) fields can, however, have '' or ' ' as a value.

c) You need a region number for the treemap_neighborhood table. I found a wikipedia page that seems to have good info. Honestly I guessed which region to use. I just needed an integer to get started. I’ll verify it later. If you aren't geo-savy, like me, this seems like a good place to start… I think… (http://en.wikipedia.org/wiki/List_of_regions_of_the_United_States - Official_regions_of_the_United_States).

d) Insert your geo data (from the new tables you just created) into the treemap_neighborhood table in order from smallest geographic areas to largest--meaning, start with neighborhoods, if their boundaries are contained within cities. Next insert your cities, then counties, then state boundaries, if your area is that large. See the following link for another explanation (https://github.com/azavea/OpenTreeMap/wiki/External-Data - geographic-regions-neighborhoods).

e) The treemap_neighborhood table becomes a layer rendered by GeoServer.

 

Connecting GeoServer to treemap_neighborhood Table:

[Alan Humphrey]

This is good stuff Kurt. Could you put it on the Wiki so everyone can have a whack at contributing to it?

In terms of setting up your layers, in order to support searches you're going to need a view. This is the one I came up with earlier this week. I haven't tested all search parameters but it *should* work.

CREATE OR REPLACE VIEW treemap_search AS 

 SELECT tp.width AS plot_width, tp.length AS plot_length, tp.type AS plot_type, tp.powerline_conflict_potential, tp.sidewalk_damage, tp.neighborhoods, tp.geometry, tp.data_owner_id, tt.species_id, tt.dbh, tt.height, tt.canopy_height, tt.date_planted, tt.projects, tt.condition, tt.canopy_condition, tt.last_updated, tt.photo_count, tt.last_updated_by_id, tt.steward_name, tt.sponsor

   FROM treemap_plot tp

   JOIN treemap_tree tt ON tt.plot_id = tp.id

  WHERE tp.present = true;

ALTER TABLE treemap_search

  OWNER TO tree_login;

COMMENT ON VIEW treemap_search

  IS 'A view to support searches from MapServer.';

Once you've got your view set up then you need to create a layer on GeoServer that uses the view.

You do not need GeoServer layers for neighborhoods or zipcodes (at least as of the 1.2 code I branched off of). Those layers are drawn in the client side javascript.

I'm still working through my implementation*. Should be tackling add/edit functionality next week.

- Alan

* I've heavily modified v1.2:

    - templates rewritten to use Bootstrap layout and to be more semantic.

    - search functionality reduced (for the time being).

    - treemap.js replaced with Dart

you can see the current state of affairs on the test site: http://canopy.greymaple.org

--

[Adam Hinz]

There's a script for the view in v1.3. Someone randomly deleted it out of the repo in v1.2.

https://github.com/azavea/OpenTreeMap/blob/v1.3/scripts/db/create_search_view.sql

-Adam

--

Adam Hinz

Software Engineer, Azavea

hinz...@gmail.com | c: 608-445-8391 | Twitter: @ahinz

[Kurt]

I just created a new page on the wiki: https://github.com/azavea/OpenTreeMap/wiki/Setting-Up-GeoServer-for-OTM

[Kurt]

Thanks for adding a little more clarification to the GeoServer layers delima. I was under the impression that the treemap_neighborhood layer would be rendered by GeoServer. Obviously the OTM community can benefit from all of us sharing what we are learning. Please feel free to edit my GeoServer page and eliminate any false assumptions I have made.

Kurt

 
On Wednesday, April 3, 2013 9:44:31 AM UTC-6, Alan Humphrey wrote: