Finalizing woodward database

I have sent the SQL files to Jianfei to upload to the database. These should be the final builds of this data, and hopefully it will work with little to no errors.

I have also been able to test the Quantum GIS tool to view the tables graphically using only the table information. This confirms that the "the_geom" column does contain all the needed information for interpreting geographic information.

The next step will be to keep sending datasets to the database and hopefully develop an entire database of two building as well as a rudimentary viewer to look at these files.

PostGIS information

Today I discovered several good websites for PostGIS information. The first lists all of the functions that are included in PostGIS and how to execute them. That site is http://www.bostongis.org/postgis_quickguide.bqg

The next thing I have found is a viewer that reads directly from PostGIS, this utility is called Quantum GIS, and it is open source and cross platform. The website is www.qgis.org

I am currently working with Jianfei to start construction of our own viewer and also uploading more datasets to the main database.

Database development in Postgresql

Database development seems to be coming along nicely now that I have a good idea of how PostGIS and PostGres work.

In order to load a shapefile into the database I simply use the shp2sql function to convert the file. This also adds a geometry column called 'the_geom' to the table that contains a rather long hexadecimal number. In order to read this you can issue SQL queries to find geometry information such as vertices, areas, and lengths. This will be how Jianfei reads in the information he needs to create an indoor viewer for the building.

Hopefully be next week I will have the kinks worked out on the GIS database end and Jianfei will have a program that can read in these variables through the libpqxx libraries for C++.

I will be going out of town for the rest of the week and return on Tuesday to start finishing this stuff up.

Uploading shapefiles to PostGIS

I will be able to upload the shapefiles of the datasets I created as soon as Scott can fix my linux account. Seems like when I was gone for a few weeks without logging in my account reset or something. So I am basically setting here with a bunch a data that I can't do anything with. As soon as I can get this data uploaded I can start using the API and bang out some code or something.

PostGIS info

I am currently trying to implement PostGIS databases for use in our project. So far I have installed the software needed to run the open source database, and I am currently reading through the documentation trying to get an idea of how it works.

Some interesting things that I would like to have are:

GiST Indexes
GiST stands for "Generalized Search Tree" and is a generic form of indexing. In addition
to GIS indexing, GiST is used to speed up searches on all kinds of irregular
data structures (integer arrays, spectral data, etc) which are not amenable to normal
B-Tree indexing.
Once a GIS data table exceeds a few thousand rows, you will want to build an index
to speed up spatial searches of the data (unless all your searches are based on
attributes, in which case you’ll want to build a normal index on the attribute fields).
The syntax for building a GiST index on a "geometry" column is as follows:
CREATE INDEX [indexname] ON [tablename] USING GIST ( [geometryfield] );
Building a spatial index is a computationally intensive exercise: on tables of around
1 million rows, on a 300MHz Solaris machine, we have found building a GiST index
takes about 1 hour. After building an index, it is important to force PostgreSQL to
collect table statistics, which are used to optimize query plans:
VACUUM ANALYZE [table_name] [column_name];
-- This is only needed for PostgreSQL 7.4 installations and below
SELECT UPDATE_GEOMETRY_STATS([table_name], [column_name]);
GiST indexes have two advantages over R-Tree indexes in PostgreSQL. Firstly, GiST
indexes are "null safe", meaning they can index columns which include null values.
Secondly, GiST indexes support the concept of "lossiness" which is important when
dealing with GIS objects larger than the PostgreSQL 8K page size. Lossiness allows
PostgreSQL to store only the "important" part of an object in an index -- in the case
of GIS objects, just the bounding box. GIS objects larger than 8K will cause R-Tree
indexes to fail in the process of being built.
Using Indexes
Ordinarily, indexes invisibly speed up data access: once the index is built, the query
planner transparently decides when to use index information to speed up a query
23

More Progress

I have finished cleaning up all the datasets for CRI and they are now ready to be exported to the database as soon as Kirk comes in.

The only thing left to be done is to finish cleaning up Woodward floors 3 and 4 and I will have all of the datasets completed, as far as rooms, stairways, and elevators go.

The next thing that I will be working on is creating floor networks for CRI and then after that is completed I am going to start working on understanding the ArcObjects model and start building things from that.

Things still to be done:

• clean up cri floors 1, 2, 3
• clean up woodward floors 1, 2, 3, 4

• export data sets to shapefiles for kirk
• upload shapefiles to a database using PostGIS and PostGreSQL
  

Data Cleanup

Today I focused mainly on trying to clean up the data that I have been creating.

There are several error and anomalies are in the datasets that I created. These include rogue polygons that were copied over from the CAD files that are no use to use, and also other irrelevant features. Some other things that need fixing are: a complete account and database entries for each and every polygon sorted correctly. This process will involve making sure room numbers and other relevant information is present in the datasets for both Woodward Hall and Cameron Research Center.

Once this process is complete I will feel comfortable exporting this data to a more permanent database using PostGIS.

Screenshots

Here are some screen shots relevant to the project, some I have posted before but this will put everything in one place:

Finished Network Datasets

I have finally completed the process of annotating the network datasets with room numbers. I have sent this information to Jianfei and hopefully he will be able to start graph creation on his end.

The next thing that I am going to start working on is connecting these graphs to existing road networks and then when I am finished with that I will begin the process of making network datasets for Cameron Research Center.