Transcript Database

www.hsvcity.com/gis
What Will We “Search By” Next?
Building Custom GIS Search Applications for the Internet
Presented By: Amy Kenum
City of Huntsville: GIS
The Problem
• Cemetery Department
– Application to Search and Locate Cemetery
Plots
•
•
•
•
•
Make it accessible by the public
Easy to use search commands
Only show specific attributes
Print maps of the cemetery
Have database updated regularly by Cemetery Dept.
History of Maple Hill
• Established around 1822, when LeRoy Pope sold 2 acres to
the city for a municipal cemetery
• Registered by the Alabama Historical Association in 1962
• One of the oldest cemeteries in the state of Alabama
• Currently almost 100 acres and over 80,000 burials
Pictures By: Felicia Sisk
List of Data Needed
• Database
–
–
–
–
–
–
–
–
–
–
Burial Block
Burial Lot
Burial Space
Interment Last Name
Interment First Name
Interment Middle Name
Person’s Title
Data of Death
Age at Death
Sex
• Map showing Spatial
Location
– Location of Burial Block, Lot
and Space
• Link
– Database
– Spatial Location
Spatial Data Evolution
4
1
4
2
5
6
3
1
4
2
5
3
6
4
2
5
6
3
4
17
5
18
5
6
4
19
5
6
36
35
1
4
2
5
3
6
34
1
4
2
5
6
3
6
4
1
4
2
5
6
3
4
4
5
20
21
5
5
22
4
5
6
23
4
5
6
3
6
1
4
1
4
2
5
2
5
3
6
3
6
4
5
6
3
1
4
2
5
3
6
44
1
4
2
5
3
6
1
45
3
1
2
5
6
3
1
4
1
2
5
2
5
33
32
31
6
1
4
2
5
3
6
3
30
61
4
2
5
3
6
BLOCK 5
46
5
62
1
1
4
2
3
6
1
2
1
2
43
60
59
5
6
3
6
4
1
4
2
5
6
3
6
4
1
4
2
5
48
6
1
4
2
5
3
6
3
49
4
2
5
3
6
1
4
2
47
1
2
3
1
58
2
3
1
57
3
6
1
4
2
5
3
6
Scroll
• Cemetery Layout
• Plot Purchaser
CAD Drawing
• Digital Cemetery Layout
2
1
56
2
3
Digital Data with Attributes
• Created Shapefiles
– Block Polygons
– Lot & Space Polylines
– Converted Labels to Points
• Created Point Database
– Burial Block
– Burial Lot
– Burial Space
Database Evolution
Record
Books
Entered Burial Location
and Interment Details
into Access Database
Access Database
Imported Data into
SQL Database
SQL Database
Building a Link
• Spatial Data (Shapefiles)
• Database (SQL Table)
Burial Block
Burial Lot
Burial Space
Unique ID to tie
tables together
B + “Block” + L + “Lot” + S + “Space”
Space ID - BCL104S5
Lot ID - BCL104S
Block ID - BCLS
Joining Tables
• 1 to 1 join
– Space ID – BCL104S5
• 1 to Many join
– Lot ID BCL104S
– Block ID BCLS
Records are old and
don’t always have the
full burial location
• Field check records with
incomplete data
• 52,000 cemetery locations
• 28,000 interment records are linked
Creating a Webpage
Designed using Active Server Pages (ASP) &
GeoMedia Web Map
www.hsvcity.com/gis
www.hsvcity.com/cemetery
Home Page
• Click to
begin Search
• Click to view
a printable
map of the
entire layout
of the
cemetery
Search Engine
Search
3 Ways
List of all the
results that match
the query with
attributes
Click Map
to find the location
of the burial plot
Barnes
Map Burial Plot
Tools
-Zoom in/out
-Go back to the
search window
-Print
-Help
Red Dot marks
the location of
the burial plot
Burial Location
And Interment
Details
Legend and
Overview
Zoom In/Out of Map
The more
you zoom,
the more
detail you
get
Maintenance
• Cemetery inputs new and updated records into a database interface
• ITS Dept. stores and backs up the data through a SQL Server
– Unique ID (number) Field
– Data Added Field
– Date Modified Field
– Exports new records into temporary table in the GIS database
• Once a week through script (local package) based on date
added or date modified
• GIS Dept. pulls in new and updated records from temporary table
– Creates ID from Block, Lot, & Space to link to spatial point
• Once a week through script (local package)
– Verify that all records join properly
– Website is automatically updated
Other Search Applications
• Other Cemeteries in the City (Pending)
• Dedicated Tree Locator (Pending)
• Bicentennial Park Brick Locator - Created using ArcIMS
Home
Page
Brick Locator
Search
Map
Mayor: Loretta Spencer
Urban Development Director: Dallas Fanning
GIS Manager: Tim Barnes
Contributors:
Webpage Design: GIS – Tom McDonough
Helped with field work: GIS – Erin Dyer
DBA: ITS – Sandra Barnes
Records: Cemetery – Brenda Webb, Felicia Sisk
Software Used:
ESRI: ArcInfo, ArcIMS
Bentley Microstation
GeoMedia Web Map
Microsoft SQL Server