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.

Codes to remember

In order to store attribute data for the network dataset nodes, I have decided to use codes in the database to differentiate each point from another. These are the codes I am using for reference:

5555 - stairway
4444 - building exit
3333 - hallway node
2222 - elevator
1111 - null

If the 'ID' attribute of the floor junctions shapefile has a 3 integer number then it is assumed to be a room number, not a code. I am hoping that using this will allow Jianfei to differentiate in his program a little easier, and give him a way to connect and represent important connection points.

Here is a screengrab of the 3d network dataset created using ArcScene, I was going to post it yesterday but Blogger was acting up:

Information about PostgreSQL and PostGIS

PostgreSQL
PostgreSQL is a powerful, open source relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It runs on all major operating systems, including Linux, UNIX (AIX, BSD, HP-UX, SGI IRIX, Mac OS X, Solaris, Tru64), and Windows. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages). It includes most SQL92 and SQL99 data types, including INTEGER, NUMERIC, BOOLEAN, CHAR, VARCHAR, DATE, INTERVAL, and TIMESTAMP. It also supports storage of binary large objects, including pictures, sounds, or video. It has native programming interfaces for C/C++, Java, .Net, Perl, Python, Ruby, Tcl, ODBC, among others, and exceptional documentation.


PostGIS
PostGIS adds support for geographic objects to the PostgreSQL object-relational database. In effect, PostGIS "spatially enables" the PostgreSQL server, allowing it to be used as a backend spatial database for geographic information systems (GIS), much like ESRI's SDE or Oracle's Spatial extension. PostGIS follows the OpenGIS "Simple Features Specificationfor SQL" and has been certified as compliant with the "Types andFunctions" profile.

Conclusions
While I have not been able to test out this database technology, it seems very promising for our applications. I will hopefully have a working version of this open source technology and have a test database set up in it. Then I should be able to evaluate the performance of this system.

Network Dataset Progress and Website completion

Network Dataset Progress
Today I have focused mainly on updating the network datasets to include attributes and have nodes for each room. This process was rather time consuming, since I had to refer to a CAD drawing to determine room numbers then manually enter in the data into the database. I have so far finished two floors and hope to have all four finished by the end of this week.

Project Website Progress
I have finally finished the initial design for the project website. So far I have all of the pages set up with some content in them and a link to this blog's RSS feed. The only thing that needs to be done is some simple formatting, maybe some graphics, and finally the remainder of content that is available. I would provide a link, but as of now the site hasn't gone public yet.