Malaysia Geographic Information System (GIS) - MyGIS

 

 

FME 2012 WorldTour

MyGIS Goes Gold with Google SketchUp Pro.

Buy Google Earth Pro now! For Inquiries:


Searching for rugged casing? We hold stock for Otter products in Malaysia www.otterbox.com Come take a look at our GIS/Remote Sensing and Geography Books, our new office is at "Kompleks Antarabangsa" on Jalan Sultan Ismail; still not far from KLCC and Kuala Lumpur convention center. We have a wide range of GIS (Geographic Information Systems) books and we will be providing more for your convenience. OtterBox Products for Malaysia and Singapore
www.ram-mount.com Check all the items here RAM Mount in Malaysia

Our Forums are back. You can check Out our MyGIS website: www.MyGIS.com.my .  

Home

Products

Services

Software
Discussion
Downloads
GIS Scripts
Careers
Training
GIS Dictionary
 

 

 

 

 

 

WHO SAID THAT ARCVIEW COULD NOT CONNECT TO ORACLE DATABASE ??

 

ArcView and SQL Connections
(By Robert Meier)

 

Connecting to ODBC data sources using the ArcView interface:

 

Arcview can access external data sources through the SQL Connect dialog. This is a pretty easy thing to do, go to the File menu on the Project GUI, then to SQL Connect.

 


 

Select the Connection you want to make. This list comes from your ODBC data sources list on your system. From the ArcView Help files:

 

To connect to SQL databases, the Microsoft Windows versions of ArcView uses Microsoft's ODBC (Open DataBase Connectivity) standard. ODBC is Microsoft's open interface for accessing data in a heterogeneous environment of relational and nonrelational database management systems.

To use your database with ArcView, install an ODBC driver for your database. Then use the ODBC Administrator to configure a data source for your database.

ArcView should work with any database for which you have an ODBC driver and any necessary database client software.

 

 

After you select the connection you might have to browse to a database or you might be asked to login. The tables’ list will reflect ONLY the tables that you have permission to access. Double click on the table name and it will move to the from: text line, then select, double click, the columns you want to include in the table. At this point you could click the Query button and you will create a table. Or you could add some logical expressions in the where: text box, like the example above.

 

If I were to write the above in a straight SQL statement it would look like this:

 

Select * from Master_Accident_Dbase where Day = ‘Wed’

 

The * means all fields. If I wanted specific fields it would look like this:

 

Select CaseNum, Location, Injuries from Master_Accident_Dbase where Day = ‘Wed’

 

 

 

Connecting to ODBC data sources using Avenue:

 

The following examples are connecting to Oracle & MSAccess.

 

The first thing to do is login/connect to the ODBC data source.

 

Access:

conSQLmdb = SQLcon.Find("MS Access 97 Database")

conSQLmdb.Login("DBQ=\\caribbean2\st.thomas\0traffic\accident\2000\2000 accident tracker.mdb")

Oracle:

SQLConnection = SQLCon.Find("TM_PERMIT")

SQLConnection.Login("SRVR=ppln;UID=avuser;PWD=jack")

 

TM_Permit is the name of an ODBC driver, in the connection string ppln is the name of the database I am connecting to.

 

After you connect/login you can use the Find request to reacquire that connection without having to re-login. So it is probably a good practice to put the connection/login code in a Startup script.

 

There are two other Avenue requests to become familiar with,

SQLCon.ExecuteSQL and Vtab.MakeSQL

 

SQLCon.ExecuteSQL will immediately execute an SQL statement on the database.

You can add, delete or insert records into a data table. Also it is possible to create new tables. See the attached scripts for some examples.

 

Vtab.MakeSQL will return selected records from the database into an ArcView virtual table object. After these records are returned you can join them to a shape file, geocode or if you have x,y’s , use that to map the information.

 

An SQL statement in Avenue is just a big string object. Get familiar with the string class and how to create complex, appended strings. Here are two examples of SQL statements generated to return records.

 

Sample:

 

 strTableSQL = “Select * from”++strTableName

vtbTable = VTab.MakeSQL(SQlConnection, strTableSQL)

 

Very complex:

 

strDateSQL = "select a.csa_assigned_to, a.action_description, b.bld_permit_type, d.vlv_desc, b.bld_develp_type,"++

"c.csm_st_nmbr||' '||c.csm_st_name address, a.csa_time2, a.csa_id, a.csm_caseno,"++

"c.prc_parcel_no"++

"from case_action a, case_bld b, casemain c, validation_values d"++

"where a.csm_caseno = b.csm_caseno(+)"++

"and b.csm_caseno = c.csm_caseno(+)"++

"and b.bld_permit_type = d.valid_value(+)"++

"and a.csa_date2 = To_Date('"+strDate+" 00:00:00', 'RRMMDD HH24:MI:SS')"++

"and a.csa_calendar_tag = 'INSP'"++

"and a.csa_date3 is null"++

"and a.csm_caseno Like 'BLD%'"++

"and a.csa_report_tag <> 'ENGS'"++

"and a.actn_code <> ANY ('FEN', 'ESO', 'ELV', 'PUB', 'FRM', 'MGD', 'MGL', 'MGT', 'SEWD', 'SEW', 'SEWI')"++

"and d.valid_key = 'Permit Type'"

 

vtbInspect = VTab.MakeSQL(SQlConnection, strDateSQL)

 

In the above statement the (+) symbol means OuterJoin the two tables.

 

Syntaxes used in SQL differ between databases. For example, in Access you need to put # signs around any date. Make sure you are familiar with the nuances of SQL in the databases you are using.

 

Here is an example of creating a list of unique values from a field in a table, this connects to an MSAccess database.

 

conSQLmdb = SQLcon.Find("MS Access 97 Database")

'conSQLmdb.Login("DBQ=\\caribbean2\st.thomas\0traffic\accident\2000\2000 accident tracker.mdb")

 

 strAcTypeSQL = "SELECT * FROM AccidentType"

 vtbAcType = Vtab.MakeSQL(conSQLmdb, strAcTypeSQL)

 lstAcTypes = {}

 for each rec in vtbAcType

   strAcType = vtbAcType.ReturnValueString(vtbAcType.FindField("AccidentType"), rec)

   lstAcTypes.Add(strAcType)

end

lstAcTypes.RemoveDuplicates

lstActypes.Sort(true)

 

 

Well, I hope this document helps you get started with Database connections and SQL in ArcView. If you have any questions you can contact me at:

 

 

By:

 

Robert Meier, GIS Analyst

City of Overland Park, Kansas

8500 Santa Fe Dr

Overland Park, KS  66062

913/895-6203

rmeier@opkansas.org

 

 

 

 

 

MyGIS Site Map

Looking for some maps of Malaysia? Click the image below

Malaysia resource for maps or simply map of Malaysia for download

Watch out! a blog!

 

Our Featured book:

Connecting Our World (Get a signed copy by the author)Connecting Our World

 
   
 

ArcView SQL Tutorial

 

ArcView-L SUM Archive

 

 

 

 

 

 


*ESRI and the ESRI Logo are licensed trademarks of Environmental Systems Research Institute, Inc.

contact webmaster

Copyright ©2001 MyGIS.  Enterprise  All rights reserved.
Powered by: MyGIS Enterprise .... member of GIS INNOVATION Sdn Bhd (557976-H)
MyGIS since August 1999
(V. 2.1)