GIS Database

Cosmos75

Registered User.
Local time
Today, 01:24
Joined
Apr 22, 2002
Messages
1,281
I was wondering if anyone here has had any experience with GIS databases? My understanding is that is is a database of
- Image Data with additional information (Layers?).

Layers?
http://www.icls.harvard.edu/gis/images/layers.html
- Political Boundaries
- Indexes
- Infrastructure
- Demographics (Population)
- Topography
- Physical Resources
- Hydrographic (water-related) Features
- Conservation/Recreation
- Regulated Areas
- Environmental Monitoring
- Coastal and Marine Features
- Miscellaneous

GIS Concepts
http://www.icls.harvard.edu/gis/mgmt1.html#2h

Is this something can can be done in Access? Or have back-end in SQL Server and Front End in Access?
 
Where I work we have a system based on GIS. We use it for scheduling routes for our field people. Since our actual database system is a bit unique, we have to export the data to Access, filter/manipulate it, then it gets imported into the GIS system. If neccessary, we can export it from GIS into access, and even create links between the two. Since we only use it for this individual purpose, I havent delved into the areas of linking the two, but I do know it can be done. The front end on most GIS ssytems is far more advanced than what you would get out of access, but you could easily use access to manipulate the data it uses.
 
jeremie_ingram,

Thank you for your reply.

I am beginning to think that an in-house GIS database would be above my head but I would like to find out if there is any part of it that I can be involved in.
 
I have concurrent interests in GIS technology and databasing. Much to my surprise, I learned taht ESRI's ArcGIS 8.1 and upgrades you integrate an Access database into that applications procedures. They call it a geodatabase.

I emphasize I am just beginning to explore this, but I had an intern with me a while ago and we messed around with field GPS data, converted to shapefiles and mapped in GIS data types.

I was beside myself when we set up a geodabase and found we could manipulate the field data directly from there and very easily.

My wheels started turning and saw developing a user friendly Access-geodatabase app that merged with graphical representation of geographical features.

I am heading for ESRI training later this month and can't wait to put this to full use.
 
SAK said:
I am heading for ESRI training later this month and can't wait to put this to full use.
SAK,

How did the training go? Did at least find out if it was feasible to create a GIS database with MS Access and ESRI's ArcGIS 8.1?
 
Hey Cosmos, actually I head to that training on Sunday, if Isabel doesn't wash ESRI of the map.

I like to mess with software so I referenced texts, online help and ESRI's on-line discussion area and such to get more of a handle on the GIS database.

Actually what ArcGIS 8.* does as just one part of the package is allow you to create something called a personal geodatabase. So I did that and when I looked at it, there was good old access staring back at me.

The geodatabase does a lot more apparently, but what I experimented with was working the the attributes of the feature shapefiles I had on hand. I exported the shapefiles into the geodatbase, then had these tables to work with, that had my data.

Using my Access knowledge, I tried out direct data entry via a quick form, then update queries and even some basic function work. Bingo, viewing the table data in ArcMap there were my updates.

So yea, lot's of potential.

One I see is setting up your GIS data in the geodatabase, then manipulating and processing the data in the geodatabase with forms, reports and all that, which makes things so much easier.

In my work, I am the first to travel this path, but I am sure what I learned is old news to others. But I will do what I can to enlighten my co-workers so someday, maybe we too, will no longer be dwelling in the digital dark ages.
 
Hi All -

Thought I'd drop my 2 cents your thread here...

The ArcGIS package allows the user to create a "personal geodatabase" as described above, to do a data wharehousing of both spatial and tabular data that, as SAK described, is able to be manipulated fairly easily... However - it is also possible to use a standard .mdb file linked through ODBC to other ESRI software (such as the predisessor to ArcGIS, ArcView 3x). However, Access does have its limitations in the GIS realm as storage of spatial data begins to take up really large amounts of space very quickly!! So the preferred method in my Department is to use SQL Server to store the tablular data and then ArcSDE (Spatial Database Engine) in conjunction with SQL Server for the spatial data storage and manipulation. To server out the GIS data we use another ESRI product, ArcIMS (Internet Mapping Service) which is a sort of ArcGIS for the web which allows all users with a web browser to use the GIS tools (not just those with ArcGIS). To keep the data current I have developed Access FE to interface with SQL Server... An example website we have just finished is located here:

http://www.mcgi.state.mi.us/MRBIS/

It was developed to assist users in finding a boating access site - navigate search or click 'Browse the Map' to see the mapping interface - this system is powered by SQL Server, ArcSDE, and ArcIMS with MS Access as the FE. for more info on all of the Arc Products check ESRI's website at www.esri.com They also have more links to other ArcIMS sites...

HTH and if you have any specific questions let me know...
Kev
 
Great post and I want to comment on that but first I wish to add more to the GIS - Access topic.

The first GIS software I used, and am still using, now along with ArcGIS, is called PCGPS made by CMT in Oregon. Good product I think.

In that package is ODBC capability, so it's actually there I got onto using Access initially with my GIS stuff. PCGPS uses, through ODBC other apps, like Excel if you so choose.

Then working with ArcGIS recently, it kind of took me by surprise, pleasantly I add, that their personal geodatabase was Access also.

I just did some mapping and reports for a project here using my GIS software for the maps, and Access for the reports. Excellent combination.

To your agencies website for boating ramp access. By the way, that portions of the website capitalize the word Access here when referring to boat ramps make me think the web author was familiar with the Microsoft product. Correct conclusion? :)

Your website exemplifies what the merger of GIS and good databasing is all about. Good job!!
 
Kevin_S said:
However, Access does have its limitations in the GIS realm as storage of spatial data begins to take up really large amounts of space very quickly!! So the preferred method in my Department is to use SQL Server to store the tablular data and then ArcSDE (Spatial Database Engine) in conjunction with SQL Server for the spatial data storage and manipulation.

...

- this system is powered by SQL Server, ArcSDE, and ArcIMS with MS Access as the FE.
My company just put on a presentation on GIS, mostly what a GIS is and what it can do. Nothing on the Software part. Knew most of what was presented just based on my research into what GIS is. They sponsored this to help the people in our company understand what GIS was and what the capabilities are.

Since the data that is use can be tremendous (hence the use of SQL Sever or Oracle) I don't think it will be something I can be a part of.

But I am curious as to how data is manipulated. Say you have data like City, Population, Area. Now you want to map out population density (Population/Area), does that require data manipulation with SQL server (or if it was a small amount if data, then would MS Access be an option?) or does ArcGIS do that manipulation readily? My understanding is that ArcGIC just presents the data (spatial) graphically as layers in a "live" map. Is that correct or have I totally missed the boat (missed finding a "boating access site":p)?
 
We use ESRI's products. There is no incompatibility between Access and GIS, but, as Kevin already observed, you generate lots of data, so Jet is not an ideal BE db. We have a call centre application that was originally developed as an Access FE with GIS integration. However this was then ported to Oracle with VB FE and is now wholely Oracle based.

We do use Access for some prototyping.

There's a nifty little application called ArcPad from ESRI. We have used this to develop a mini db for use on our handhelds which we then upload to an Access db for reporting.
 
I'm glad that I stumbled on this link. I've been doing Access development for a couple of years now, first at IBM(!) and for the last month or so for a mid-sized mortgage lender. Though I love Access development, writing applications for industries which don't interest me personally has been a bit of a drag. GIS seemed to be a good logical step as my undergrad was in Archaeology and combining technology with a Social Science seems intriguing. So all that said, what I'm getting at is:
-Some decent links on GIS training (have seen programs at various local colleges and universities, but obviously don't want to get into a diploma program)
-How those of you who do work in GIS feel about your career decision.
 
I don't work in GIS, but three of my colleagues do. This is a growing area for us (no pun intended) in local government.

We depend mostly on ESRI training.
 
I was wondering, if there is any way i can develop my Geodatabase without the need for ArcEditor or ArcInfo??
I have just recieved my arcGIS 9.1 but it only came as ArcView(standalone licensee). I have been going through the geodatabase tutorial but i cant edit(i need more extra licensing) any geodatabase with SDE data,subtypes or geometric networks.

Any idea will be highly appreciated
 
Last edited:
I work with GIS as well. During my studies i used Esri (Arc) products. Now i work more with MapInfo.
Access can be used for GIS-purposes. For example in MapInfo you can link your geographical information to access tables. So you can do the geographical analysis in the GIS-system and store the rest of the data in access or another database.
There are also several programs that provide (some) map-display options for access database. For example take a look at the demo of SylvanMaps at
http://sylvanmaps.com/screen_shot_of_the_baja_2000_vb6.htm
or
http://sylvanmaps.com/VBSamples/baja2k.zip
Unfortunatly this is not a free GIS-program (the demo is though), but if you take some time you'll find some free gis programs (e.g. http://freegis.org/)

Regards
Steven
 
That Link is very Ballistic. Thanks.
 
I've been working on and off with databases and GIS for 6 years now...

We have a land register system that holds planning / housing and management information within access maps are accessed from a button behind which there's some VBA that takes you through to ArcView 3.x. The link between the two is a simple key field and the VBA opens up a project and passes across a couple of arguments to an Avenue script that then focuses the map onto a particular area based on a particular polygon file that has the same key referencing system.

That way all our local plan and housing application sites can be quickly viewed by anyone who knows anything. The application itself doesn't do any real analysis of geographical information just transfers you through to a map. Link this in with photos of the area stored in the database and you have a nice tool for keeping track of what's happening with pieces of land.

We have ArcGIS 9 in the office and will be re-writing this code so that we can do the same thing..

Works great..
Cosmo layers can be added but they won't necessarily have links through to the database unless you write code to make them link.

We are obviously not holding any of the shape files in the database - holding information such as mastermap within a RDBMS such as oracle is when you start to having to think about ARCSDE...

The standard line from ESRI is that ESRI desktop software such as ArcView requires ArcSDE to be in place as an application server to support operation with an RDBMS. Suitable RDBMSs include Oracle and SQL Server. If Oracle is used then there is a choice of storage formats including Oracle's own SDO format.

When an ArcSDE-enabled RDBMS is implemented then ArcView desktops only have the ability to read this data - not to update it. This requires a more expensive ArcEditor license. ESRI's thinking on this is that there should be a GI data custodian who is responsible for and authorised to update the central corporate datastore. The custodian would have the ArcEditor license and would receive shapefiles (for example) from departments and check them before submitting into the corporate datastore. This process should ensure that appropriate metadata is provided.

Other packages such as MapInfo can read SDO direct and don't need the ARCSDE - approach vendors to confirm this detail ..
 
Lightwave, thats great to know that there is some hope. May you please help and post the Avenue script and the VBA code behind that button that does all this. I forced my boss to buy me ARCGIS 9.3, and while i was away someone oredered for a standalone version of ARCVIEW 9.3. So when i got into the hood,i realised we needed to buy arcEditor and arcSDE. This put me off and i descided to put off that part of the project and upto now, its still pending.
Now i do my applications both in VB.NET and access, but i am still bieng pushed to implement this.
Your detailed help with the script and the code will be highly appreciated.
 
Code --

Bearing in mind this is very specific to our set up..
For instance the location of the executable will be different for your location and the reference to the necessary fields will be completely different as well. You will also need to substitute in the name of the theme that has polygons with the necessary "Key field". In that effect ArcView is very much acting as a repository of a separate table with a one to one relationship with the ID field in Access...

Also bear in mind that this links an Access 2003 application to ArcView 3.1 in Windows 2000 if you are in Windows XP you need to use ArcView 3.3 and I think add a patch to 3.3 to the listed Avenue code should then work.

Also note that the Avenue script may need to be re-written in VB.net depending on whether ArcGIS supports Avenue or not.

Finally this is not a Geodatabase solution (see also Kevin S post for explanation) - it is linking a polygon through to a reference field in a database...Personally though I think it has several advantages over ArcSDE option - it separates the digital geometry from the attributes (so what if you can hold grid references information in a database ) it's hardly a burning desire to look at 40 million 6 digit numbers, shades of Matrix or what) - it means you don't need to buy ArcSDE (expensive) - and anyone can edit the polygons (saves you on the purchase of ArcEditor).

Note ArcGIS will link direct to Access without ArcSDE as a "Personal Geodatabase" option. The implication is that it won't work with more than one user over a network. This is a true geodatabase as mentioned by some of the other contributors above. We've set such a personal geodatabase up and so far works great. We are going to try and get multiple users to link to this geodatabase and just see what happens...!!!

My thanks to a colleague of mine John Thain who wrote this code and has kindly agreed to it's publication...

Code behind the access button : -

Private Sub CGetMap81_Click()
Dim sScript As String, sEasting As String, sNorthing As String, sGridrefs As String, sID As String
Dim sCommand As String, sAVTheme As String
sAVTheme = "GlobalHousing"
Const NO_DATA_ERR = 94
Const AV_NOT_LAUNCHED = -2147352571
On Error GoTo StartArcView
'-------------------------------------------
'use values from current record using DAO
Dim rst As DAO.Recordset
Set rst = Me.Recordset
sEasting = Str(Me!EAST)
sNorthing = Str(Me!NORTH)
sID = Str(Me.ID)
sGridrefs = sEasting & "," & sNorthing
'-------------------------------------------
sScript = "WLCEd.ZoomToSite"
sCommand = "av.RUN(" & Chr(34) & sScript & Chr(34) & ",{" & Chr(34) _
& sGridrefs & "," & sID & "," & sAVTheme & Chr(34) & "})"
ContinueAfterLaunchingArcView:
lngChan1 = DDEInitiate("ArcView", "System")
DDEExecute lngChan1, sCommand
Application.DDETerminate lngChan1
Exit Sub
StartArcView:
If Err = NO_DATA_ERR Then
MsgBox "Can't locate housing site, no grid refs.!!"
Exit Sub
ElseIf AV_NOT_LAUNCHED Then
Shell "c:\ESRI\AV_GIS30\ArcView\Bin32\ArcView.exe E:\arcview.dat\projects\mbprojects\p079.apr", vbNormalFocus
Err = 0
Resume ContinueAfterLaunchingArcView
End If
End Sub

And the Avenue Script : -

'*******************************************************************
'* Name: WLCEd.ZoomToSite *
'* Text: zoomsite.ave *
'* Author: John H Thain *
'* *
'* Headline: Zooms to extent of housing site *
'* *
'* Self: 0 - the X coordinate (Easting) *
'* 1 - the Y coordinate (Northing) *
'* Returns: Nothing *
'* *
'* Description: Zooms to extent of site + 1000 *
'* NOTE - this script is designed to be run from *
'* Microsoft Access database *
'* It is not assumed that a View is the active *
'* *
'* History: Wed 16 Oct 2002 - Original Coding *
'*******************************************************************
args = Self.Get(0).AsTokens(",")

xCoord = args.Get(0).AsNumber 'not required but passed from Access
yCoord = args.Get(1).AsNumber 'not required but passed from Access
siteID = args.Get(2).AsNumber 'site reference
housingTheme = args.Get(3).AsString 'GlobalOpenSpace

'msgbox.Info (siteID++housingTheme.AsString, "Info")

'Restore the ArcView window
av.Restore

'thePoint = Point.Make(xCoord,yCoord)

viewList = {}
for each d in av.GetProject.GetDocs
if (d.Is(View)) then
viewList.Add(d)
end
end

for each theView in viewList
for each t in theView.GetThemes
if (t.AsString = housingTheme) then
t.SetVisible(TRUE)
theHousingFTab = t.GetFtab
theBitmap = theHousingFTab.GetSelection
' build a query string
theQuery = "([Id] = "+siteID.AsString+")"
'msgbox.info (theQuery.AsString, "Info")
theHousingFTab.Query(theQuery, theBitmap, #VTAB_SELTYPE_NEW)
theHousingFTab.UpdateSelection
r = Rect.MakeEmpty
if (t.CanSelect) then
r = r.UnionWith(t.GetSelectedExtent)
end
if (r.IsEmpty) then
return nil
elseif ( r.ReturnSize = (0@0) ) then
theView.GetDisplay.PanTo(r.ReturnOrigin)
else
theView.GetDisplay.SetExtent(r.Scale(1.1))
'-----------------------------------------
d = theView.GetDisplay
s = theView.ReturnScale
'msgbox.info (s.asString, "Info")
'-----------------------------------------
'if (s < 1000) then
d.ZoomToScale(s + 1000) 'Mark scale of extent of site + 1000
'end
end
end
end
theView.Invalidate
end
 
Last edited:
Thanks a Bunch Lightwave, but i will need some more help here.
I put the code behind a button,created three text boxes on the form and named them ID,EAST,NORTH as required. I created the script in an apr and named it WLCEd.ZoomToSite as required.
Ok. i click on the button and it opens arcview! WOW!!

My question is can someone pass values from an access form into arcview and it automatically updates the table of the shape file or rather map? If so, may you please tell me how to do this and post one of the .apr and a shapefile used for this. I really wanna see how this comes out really. Hey! Say thanks to the owner of the Code(John H Thain).
 
Last edited:
The short answer is yes

The rest of this note explains the process in ArcView 3.1 – the exact process will vary in ArcGIS but I suspect will be approximately the same…

Essentially what you need to do is set up an odbc link between the access table you are interested in and the arcview theme that means that on opening the apr ArcView/GIS drags through the information from the database…

Within Windows 2000 you do this by -
Start/Settings/Control Panel/Administrative Tools/Data Sources (ODBC)

Hit Add button and select the required driver (eg MS Access) ….

Once you have selected the driver a new window will appear within which you select the data source and give the ODBC link a name eg JohnWayneDatabase

Now select the database you require by hitting the Select button and using the file dialog box to navigate to the appropriate database from which you would like to import information… eg JohnWayneDatabase.mdb

Next go to your Arc Application and open the project referred to in the VB script… Part of the VB script that I posted both opens ArcView but it also tells ArcView to open a particular project – In my listed code this was “p079.apr”

This apr is where you need to place the avenue script it is also the apr within which you are going to link your database to a particular theme via the ODBC you just set up..

Open your appropriate apr (in my case p079.apr) and go to the Project overview startup and within the Project Menu hit SQL connect

A new window will appear that in the top left has a combi box that is indicated as Connection… Hit the down arrow and scroll down through the list to the ODBC link that you previously defined (eg JohnWayneDatabase)

Now hit connect – a list of tables and queries should now appear in a box beneath the connection from which you should double click the table from which you require information to be connected through to your apr… In my case one particular table in my example JohnWayneDatabase has The names of all the differing ranches within the state along with how much money each owes me…

Once you have selected the table you also need to select either all or some of the fields within that table before hitting query..

On hitting query a new table should be created within your apr that drags all the required information from your database into that table…

The next step is to link the imported table to the pre-defined polygon layer of ranches… using the important ID field that independently were defined on one side within the database and on the other side within the ArcView – by simple digitisation..

My guess is that if you are just starting this process off you will need to go through and digitise a layer or alternatively edit a layer and make sure that the polygon layer has a key reference field that relates to the database… (there is no way getting round this!!)

In the above case my theme (let’s call it ranches.shp) would just be a set of polygons with a field marked ID – every time a new ranch came on board I would add a polygon to this shape file and a record in the database and give each a unique ID (usually a number) let’s say 100 that I have ensured relates to each other. If you are in access and you are using the autonumber feature then when naming the polygon field reference ID you have no flexibility in naming the related polygon (it will need to be 100).

Right – now within the Tables menu of ArcView select the Join option and join this ODBC table to the ranches.shp on the Fields that relate to each other (probably ID)

Now save the project –

Now anytime you open that project – the ODBC link will drag uptodate information across from the database into this table and link it to that polygon file…

You can then use this linked attribute table to for example make a legend of all the ranches that owe you money how much they owe you and steal the appropriate number of mustangs…

The posted avenue and VB script not only tell the computer to open ArcView and what project (apr) to open in this case p079.apr but it also indicates what theme to open. In the listed case this was GlobalHousing (simple theme of housing applications within our district) for the JohnWayneDatabase example this would be Ranches (note it is the name referred to in the View and NOT the actual name of the shp file..)

So the ID field within the code needs to relate exactly to an ID field within the Ranches / GlobalHousing Theme – and it programmatically “Zooms to extent” (You can do the same thing manually as the program – go to table highlight the record then back in view hit the “Zoom to extent” Button)

The information within the view will be a snapshot once it is imported however anytime you open up the project it will refresh the ODBC link so in practice this is never a problem. If you want to change information within ArcView it is a case of going into the Access application and altering the information there and the next time you open the ArcView application the updated information will be dragged across (Status in the bar will show that it is doing this everytime you open the project)

Mark
 
Last edited:

Users who are viewing this thread

Back
Top Bottom