Create New Postgres Users and Database Maintenence

Procedure for creating a new postgres user with proper attributes:

Use either:

CREATE ROLE name LOGIN;
or
CREATE USER name;

(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default)

Some Important Attribute and Privilege Changes for Users:

to allow database creation for user:
ALTER ROLE name CREATEDB;

to set a password for user:
ALTER ROLE name PASSWORD 'password';

to grant/revoke public access on a table or database can replace ALL with select, update, delete, etc:
GRANT/REVOKE ALL on tablename to user;

Database Maintenece and Backup Procedures:

Performing an SQL dump and retrieving the dump(generates SQL files for all tables in database and dumps to a outfile for backup):
pg_dump dbname > outfile;
psql dbname < infile;

or use:

pg_dumpall > outfile;
psql -f infile postgres;

Performing VACUUM maintentence to recover disk space and update database statistics:
VACUUM ANALYZE;
or just VACUUM;

Reindexing:
REINDEX;


These tasks should be performed every week to make sure our system is backed up and running optimally.

Project Update 9/19/2008

This week we have been working on finishing the implementation of hallway segmentation and multi-desktop connections.

I have finished updating the woodward and cameron files with hallway polygons so that we can facilitate blocking/segmentation. This seems to be working well and Jianfei has implemented these changes in his program. We now have the capability to choose polygons and block them off, as well as stairways and elevators, and entrances.

The issue of multi-desktop connections works in theory, the only problem is we have not been able to connect to PostGIS remotely at the moment. However, we have been able to make changes on two program running on the local machine and were able to see dynamic updates when a section of the building has been blocked off. Next week we should be able to fix the remote connection problem so that we can log in and change things from another machine.

Progress of digitizing the atkins floorplans is very slow at the moment, currently I only have the polygon data converted from CAD. I have been experimenting with different spatial transformation algorithms to line the data up but them keep coming up very skewed. I have since went back to my old method of manually identifying a building footprint and aligning all other layers to this contour file. I hope by next week I will have a final model of the building to put in the database, and in subsequent weeks I will begin the process of identifying polygons and annotating them for use in our program. Then the last step will be creating the hallway networks, which I am very unsure about because of the complexity of this building.

Here is a screenshot of my progress digitizing cameron, this is the first floor:

Changes in Mid-level abstraction and segmentation. Change in line file data model...

We have decided to change the way we are handling the mid-level abstractions for the program. We have ditched the idea of trying to derive our own segments arbitrarily, and opted for a method that will hopefully allow well-defined segments with a better way to select/block segments of hallway.

What we have decided to do is incorporate hallway polygons into our displaying layers that will define segments of the hallway. These segments have already been defined, and are derived from the CAD files. Each of these polygon segments (see Fig.1), and their corresponding line files will be labeled with a "SEGMENT_ID" that determines which segment a hallway line file belongs to.

These well-defined hallway polygons will also be used to allow the end user to select large "chunks" of hallway and block them off, or view more detail about a particular one. The problem we were facing before was defining hallway segments, and which corresponding line segments from the line files to include.

Due to this change in our abstraction method I have changed the Data Model for the line files that correspond to each building level.

The new model includes the following attributes:

OBJECTID
SEGMENT_ID
BUILDING
WEIGHT
flr_num
COMMENTS
SHAPE_LENGTH

Figure 1:


This screenshot details the hallway segmentation that we will be using, the selected area is a well-defined segment.

Project Update 9/5

This week we have been working on a method to facilitate the course-level abstraction we have been discussing. For now I am manually identifying different segments of the building that will be separate in Jianfei's course level model. These segments of the building can be blocked off or displayed separate from their constituents. In the future we hope to make this segmentation feature automatic, but for the sake of time I am manually identifying and labeling them for use in our demo. Progress on a demo is nearly complete and we should have something to present by early next week. We are also striving to have communication to the database established between multiple clients in order to facilitate external changes to the DB. I have also started converting the Atkins library files over to GIS data and the final product should be available within a couple of weeks. I am detailing the steps I go though to convert the data to what we need for this project, and I will provide a document detailing the steps and completion time for them. This document should provide a basis for the process that will be required if we move to automated conversion of this data. Another avenue I am exploring is the use of Archibus data in place of CAD files. Our campus supposedly has Archibus files for each building on campus and judging from this article: http://www10.giscafe.com/nbc/articles/view_article.php?section=CorpNews&articleid=500611, et al, this data can be incorporated into ArcGIS very easily now. I have emailed Fred about access to the Archibus system and the details of this kind of data, I am just awaiting a response.

New CAD files

I have finally received the new CAD files for the library. Throughout this week I am going to be starting the process of converting them to use in our project. I am also going to make a detailed description of how I convert the data so in the future we may move to an automated process for this.

Some other things that are going on is segmenting Woodward hall and also finishing up some scenarios/demo that we can show off.

Tuesday

Today I added the "segment" and "weight" columns to the line files for each building. I am still not sure about what to do with the segment column so I am just arbitrarily selecting zones and lableling them. I am not going to spend much time on this because I have more important things to do. This includes:

Adding and converting new building from their CAD files and exploring the possibliity of using Archibus for data. I also would like to get a new version of the project web site up and running as soon as I possibly can. ( preferably by this weekend)

Connected Indoor Graph to Outside Road Network

I have completed updating the line files for the indoor built environment so they now connect to the external road network. This was made possible by editing the existing line files so they connect to points in the road network junction file.

Jianfei is now undergoing testing so we can begin setting up scenarios. We now also have the capability of searching for a geocoded address and having a marker indicate the location of the typed in address.

Here is a screenshot showing the newly completed model:

Scenerios and website progress

Over the past week I have been working on connecting the line files to the existing road networks. This will allows us to facilitate route-finding on a much larger scale and begin to develop a few scenarios that we can test. I have also started to re-design the website, and add more content to make things a little more informative and navigable.

I would also like to start refining our map design and layout so that it's as informative as possible without having too much information. This should be finished in the coming weeks.

New Website location, finished updating of woodward junction files ot 7-digit format

The website is now sitting at http://www.viscenter.uncc.edu/nij-gis/home.htm which is where it will be hosted from now on. The link to this blogs feed is also available from this index page. I hope to start finalizing the design of the site by next week and have more content available.

I have also finished updating the junction file for woodward hall to reflect the new 7-digit identifier code that Jianfei and I came up with. It seems he has also been able to load the cameron database in his graph program with only a few errors. I am in the process of working these out and hope to have a final display by the end of the week.

All CRI files completed

I have finally finished uploading the last of the files to the PostGIS database for CRI building. The junction files contained also have the new annotation that we have developed, even though it's much more time consuming and a bit redundant.

Some things that still need to be done are:

• Finish up web page design, and secure space on the webserver for this page to be stored on
• Eliminate errors from the datasets as we come across them
• Change the woodward junction file so that it has the new annotation that Jianfei's program will support
• Get the Interoperability extension working so I can read/write directly to the PostGIS database