transbasesf

Disclaimer

This data is being provided as public information as defined under San Francisco and California public records laws. The San Francisco Department of Public Health (SFDPH, or the Department) cannot limit or restrict the use of this data or its interpretation by other parties in any way. Where the data is communicated, distributed, reproduced, mapped, or used in any other way, the user should acknowledge SFDPH as the source of the data, provide a reference to the original data source where also applicable, and note any caveats specified in the associated methodological documentation provided by the Department. However, users should not attribute their analysis or interpretation of this data to SFDPH. While the data has been collected and/or produced for the use of SFDPH, the Department cannot guarantee its accuracy or completeness. As all data is associated with methodological assumptions and limitations, the Department recommends that users review methodological documentation associated with the data prior to its analysis, interpretation, or communication.

Instructions to Setup TransBASE Components

The first step is to download PostgreSQL/PostGIS. I prefer the package created by EnterpriseDB because it includes Apache (version) with PHP enabled and a stack builder for enabling PostGIS/ODBC in one install. You can download EnterpriseDB's PostgreSQL installer here. If you would like to use PostGIS with ESRI ArcGIS I recommend installing version 9.1 - check out this link for more information.

Run the installer to setup PostgreSQL and click Next.

Select a location to install PostgreSQL. I'm going to go with the default installation location for this tutorial.

Select a location where your data will be stored. Again, I'm going to go with the default.

Create a password for your superuser account. By default this user will be given the name postgres. Make sure you write your password down.

Enter the port number you would like PostgreSQL to listen on. I'm going to use the default of 5432 for this tutorial.

Select a language.

Finally, PostgreSQL is ready to be installed. Click Next to continue.

At the end of the installation you will be prompted to Launch Stack Builder. Make sure this option is checked and click Finish.

Select your installation of PostgreSQL and click Next.

Check the following applications for installation: psqlODBC (if you want to use ODBC with your database); PostGIS 2.0 under spatial extensions; and Apache/PHP v2.4.12 under Web Development. A screen will appear confirming your selected packages. Use the default directory and click Next.

Each package you selected will then install. Click next until finished. For this tutorial I am using the default installation location for all of these components. Please use port 80 for the Apache/PHP server.

When you get to the PostGIS installer make sure "create spatial database" is checked.

Again, select an installation location. It should default to the location of your PostgreSQL folder.

Enter your postgres superuser information and click Next.

When asked to register an environmental variable for GDAL_DATA click Yes. GDAL is the library PostGIS uses to do Raster transformations. You can learn more about this cool project here.

After the installation of all the packages is complete verify that Apache has been installed correctly by opening your browser and navigating to http://localhost/.

Next click on the Start Menu an open pgAdmin. pgAdmin is a create GUI for accessing and querying your PostgreSQL databases.

Double click on your PostgreSQL instance. You will be prompted for the password you setup during installation. You should see two databases: postgis_template and postgres.

Let's create a new database for all the data in TransBASE by restoring the pg_dump found on GitHub. Right click on your instance of PostgreSQL and select New Database. Give it a name. In this tutorial I'll be using transbase_demo. Set the owner of this new database to be your postgres superuser.

Next click on the Definition tab at the top of the window. Use the postgis_template as the template for this new database. The postgis_template has the PostGIS extension enabled and includes all the internal tables PostGIS will need to create geospatial data. Use UTF8 for encoding. Click OK to create a new empty database.

Right click on transbase_demo and click Restore. In the window that appears select Custom or tar from the dropdown box and navigate to the pg_dump of TransBASE you downloaded off GitHub then click Restore.

You may receive a message that the role "opendata" does not exist. A read-only role is current used on TransBASE with the name opendata. You can create this role at a later time if needed. This error will not impact the tutorial.

Double check that the pg_dump has been restored properly by checking out the tables under the public schema.

Create a spatial query to ensure PostGIS is running properly with the new data. Click SQL button in the top tool bar and enter the following SQL statement:

SELECT count(vw_geo_intrsctn_switrs_all_types_cyc_col_prty_vctm.unique_id), geo_bndry_neighborhoods.neighborho FROM vw_geo_intrsctn_switrs_all_types_cyc_col_prty_vctm, geo_bndry_neighborhoods WHERE ST_Intersects (vw_geo_intrsctn_switrs_all_types_cyc_col_prty_vctm.geom, geo_bndry_neighborhoods.geom) GROUP BY geo_bndry_neighborhoods.neighborho ORDER BY count(vw_geo_intrsctn_switrs_all_types_cyc_col_prty_vctm.unique_id) DESC

This query will show a count of cyclist collisions by neighborhood in San Francisco.

You will need to setup your firewall to allow incoming and outgoing connections on ports 80, 8080, and 5432. Instructions for doing this on Windows can be found . Next you will need to set PostgreSQL to allow connections from any IP address. You can quickly access pg_hba.conf using pgAdmin. Click on Tools, Server Configuration, then click pg_hba.conf.

Double click the top host.

Change the IP Address to 0.0.0.0/0. This will allow any IP Address to connect to this instance of PostgreSQL. You could also limit this IP address to the range of your internal network if desired.

Next you will want to setup Apache Tomcat. Tomcat is a Java servlet that we will use to run Geoserver and MapFish Print. The first step is to install a Java Runtime Environment (JRE). In this tutorial we'll run Java SE Runtime Environment 8 64bit.

Double click the installation file after it has finished download. We're going to all Java to install in the default location. Click Install.

After Java has finished we will need to create an environmental variable to our Java environment. In the Control Panel navigate to Systems and Security then Systems - click Advance system settings on the left hand site.

In the System Properties window click the Advance tab.

Click the Environmental Variables button toward the bottom of the window and create a new variable with the name JAVA_HOME and variable value that points to your ...\Java\jre folder.

Next we will need to download Tomcat. For this tutorial I'm going to use Tomcat 7. Download the 32/64-bit Windows Service Installer.

After the installer downloads run it and click next when the Tomcat Setup screen appears.

Select the Normal installation and click next.

Use the default settings. In this tutorial Tomcat will be operating on port 8080. Click next.

If you set up your JAVA_HOME environmental variable correctly the Tomcat installer should recognize the location of your JRE's installation directory. Click Next.

Pick a directory to install Tomcat or use the default location.

When the installation wizard is complete check Run Apache Tomcat and click Finish. Tomcat will now start and run as a service.

Open your browser and navigate to http://localhost:8080/. If Tomcat has been installed properly you should see the following page:

Next you will need to download a Geoserver web archive (WAR) to run on the Tomcat servlet. You can obtain the WAR file here. Geoserver will allow data stored in PostGIS to be served out in a variety of OGC compliant forms and consumed by a client application.

Download the latest Geoserver WAR (at the time of this writing 2.7.1), unzip the file, and save the .war file into the following directory: C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps.

Next you will need to add a role and user to access Tomcat's manager interface. Navigate to the following file: C:\Program Files\Apache Software Foundation\Tomcat 7.0\conf\tomcat-users.xml and open the file in a text editor.

Add the following role and user in between the <-tomcat-user> tags:

Open the Tomcat manager (bottom right side of Windows bar at the bottom of the screen) then stop and start Tomcat.

Navigate to the Tomcat web application manager interface located here: http://localhost:8080/manager/

In the manager interface you should see Geoserver as an application. Double check that the service is running.

Next navigate to the following web address: http://localhost:8080/geoserver. You should see the default Geoserver manager page.

The default user name for logging into Geoserver is "admin" and the password "geoserver." Make sure to change these to something more secure using the web manager. After you login you should see a screen similar to the one below.

Some of the layer styles used in TransBASE require the Web Protocol Service (WPS) extension. This is not natively installed with Geoserver and will need to be added manually. Please go to the following website and download the appropriate zip file for your version of Geoserver.

Unzip or copy/paste the files you just downloaded to the following folder: C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\geoserver\WEB-INF\lib.

Restart Tomcat and log back into the Geoserver manager. On the left side of the page under Services an image for WPS should now be shown. Click WPS and verify that it is enabled.

Next you will need to overwrite Geoserver's workspace with TransBASE's styles and layers. Copy the following folder from your Github download and paste it in here: C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\geoserver\data. If asked if you would like to merge and overwrite the existing workspaces folder in Geoserver click yes.

Double click on the workspaces folder and delete all the other folders except transbase_public.

Double click on transbase_public and open datastore.xml in a text editor. You will need to change the database stores properties to match your PostGIS instance.

Navigate to the section of XML under <connectionParamters>. Change the following parameters to match your PostGIS instance:

<entry key="passwd">[your_password]</entry>

<entry key ="database">[name_of_your_transbase_database]</entry>

<entry key="user">[name_of_your_user_if_not_postgres]</entry>

Save this file and restart Tomcat.

Log back into the Geoserver manager and click on the Layers link on the left side of the page. You should now see several layers/views being pulled from your PostGIS instance.

Next you will need to configure Apache and Tomcat to work together on the same port. Some of the pages that make up TransBASE utilize PHP which Apache can easily process. An alternative would be setting up Tomcat to use PHP but I've never had any luck getting it to work properly. Navigate to the following — C:\Program Files (x86)\PostgreSQL\EnterpriseDB-ApachePHP\apache\conf — and open httpd.conf in a text editor.

Search for the following modules and uncomment (remove the # sign) in front of them:

modules/mod_proxy_http.so

modules/mod_proxy.so

Near the top of httpd.conf you will need to set up a virtual host to route traffic for the Tomcat serevlet to Port 8080 while allowing PHP based pages to be processed by Apache Server. Copy the following text under "Listen 80":

NameVirtualHost *:80

<VirtualHost *:80>
ServerName [your_ip_address]/transbase
ProxyRequests Off
ProxyPreserveHost On

<Proxy *>
Order deny,allow
Allow from all
</Proxy>
ProxyPass /metadata/metadata.php !
ProxyPass /metadata/connection.php !
ProxyPass /headerimages/rotate.php !
ProxyPass /bugreport/bugpage.php !
ProxyPass /newdatasets/newdatasets.php !
ProxyPass /youtube/youtube.php !
ProxyPass /metatable/metatable.php !
ProxyPass /connection/connection.php !
ProxyPass /faq/faq.php !
ProxyPass /logo/transbase_logo.png !
ProxyPass / http://[your_ip_address]:8080/
ProxyPassReverse / http://[your_ip_address]:8080/
</VirtualHost>

Make sure to replace the bracketed text with your IP address.

The next step is to set up a Connector port on Tomcat to redirect outbound 8080 traffic back to Apache on port 80. Copy the following text into C:\Program Files\Apache Software Foundation\Tomcat 7.0\conf\server.xml under <Connector…>:

Save the file when you’re finished.

Copy and paste the transbase folder into your webapps folder on Tomcat — C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps.

Open C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\transbase\scripts\heron-config\DefaultOptionsWorld.js and open it with a text editor. Use find and replace all to replace "transbasesf.org" with your project's IP address.

Next copy over the ROOT folder into the webapps folder on Tomcat. If asked, say yes to overwriting any of the existing files in the ROOT folder.

Open up C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\ROOT\index.html in a text editor and change "transbasesf.org" to your domain or IP address.

Next copy all folders in the transbase apache folder over to here: C:\Program Files (x86)\PostgreSQL\EnterpriseDB-ApachePHP\apache\www.

Copy the print-servlet-2.0-SNAPSHOT folder into C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps. In order to enable MapFish Print you will need to open C:\Program Files\Apache Software Foundation\Tomcat 7.0\webapps\print-servlet-2.0-SNAPSHOT\config.yaml and change the host to your IP address.

MapFish also requires an installation of Python 2.7 to operate. You can find Python here.

Run the installer and click Finish when it has completed.

Open up the Services window and restart both EnterpriseDB ApachePHP and Tomcat.

Navigate to your IP address in a web broswer and it should redirect to the TransBASE web application. Turn a layer on to make sure everything is working properly.

Click the printer icon and verify that MapFish Print is working correctly.

That's it. The next steps would be to replace data in PostGIS with your own and modify the layers in TransBASE as needed to match your needs.

devmorris/transbasesf

transbasesf