Category Archives: GIS

Read the Descriptions from all columns in MS Access database

Posted on by
Reply

From one of my developers. This VBA (couldn’t find an approach in .Net) will loop through all the tables and write out the description from all the fields.

 

TODO’s include grabbing descriptions for tables and then tweaking below to create ALTER statements so that the comments can be applied to a server RDBMS after migration from Access (since almost none of the migration utilities I’ve seen migrate this documentation from Access).

 

Option Explicit

[cc lang='vb' ]‘call readAllTables
Public Function readAllTables()

Dim DB As Database, tbl As TableDef, fld As DAO.Field

Dim RS As Recordset
Dim Table As String
Dim allDesc As String

Set DB = CurrentDb()

For Each tbl In DB.TableDefs
If Left$(tbl.Name, 4) <> “MSys” Then
‘Debug.Print “In Table ” & tbl.Name ‘& “      ” & tbl.DateCreated & “      ” & tbl.LastUpdated & “     ” & tbl.RecordCount
allDesc = allDesc & vbNewLine & “Table:” & tbl.Name
‘ optional code to print all the fields
On Error Resume Next
For Each fld In tbl.Fields
‘Debug.Print fld.Name
allDesc = allDesc & vbNewLine & fld.Name & “:” & fld.Properties(“Description”)
Next fld
End If
Next tbl

WriteToATextFile (allDesc)
End Function

Sub WriteToATextFile(ByVal outputStr)
‘first set a string which contains the path to the file you want to create.
‘this example creates one and stores it in the root directory
Dim MyFile As String
MyFile = “c:\” & “TableFieldsWithDesc.txt”
‘set and open file for output
Dim fnum As Integer
fnum = FreeFile()
Open MyFile For Output As fnum
‘write project info and then a blank line. Note the comma is required
Write #fnum, outputStr
Write #fnum,
Close #fnum
End Sub[/cc]

Accessing versioned geodata in ArcSDE with SQL

Posted on by
Reply

This is excerpted from the following forum post:

“We’re running SDE and have several featuresets with versioning turned on…. We have numerous non-GIS applications that use SQL queries to access information from the spatial data. However, we don’t get all the features that I’m expecting to be returned. How do I access the features that have been added/deleted (and are, therefore, “hidden” from a straight SQL query)?”

As long as it is just the attributes that you are after, you can set up a multi-versioned view using sdetable.exe.

sdetable.exe -o create_mv_view -T mvv_wMeter -t wMeter

Then on your db connection, you execute the set_current_version stored proc to set the version, then issue your select statement on the multi-versioned view:

exec sde.set_current_version 'SDE.SOMEOTHERVERSION'
GO
Select COUNT(*) from mvv_wMeter ;

i don’t have it my example, but sdetable.exe needs db connection info arguments: -s,-i,-u,-p,-D in some combination depending on your config (I usually just set the SDEDATABASE,SDEINSTANCE,SDESERVER env vars in a batch file–sdetable use the env vars if they exist). – Jay Cummins Aug 6 ’10 at 19:39

I think this ESRI support page may be relevant.

I believe it is worth posting the warnings on that page here:

Never use database management system (DBMS) tools to update any row ID (object ID) field maintained by ArcSDE in the database. These object ID fields are allocated and managed by the geodatabase and, therefore, should not be altered using SQL.

Never edit the DEFAULT version of the geodatabase using SQL. Starting an edit session on a version obtains an exclusive lock on the state that the version references. If you lock the DEFAULT version, you prevent ArcGIS users from connecting to the geodatabase.

In the 9.3 help page they also warned against editing non-simple feature class attributes (Geometric Networks, Topologies, etc.) via SQL.

Remove header from a text file

Posted on by
Reply

I’m processing some Darwin-schema biodiversity text files in preparation for GISing them and I need to trim the headers from the tops of the files before importing them to the database. Of course, *NIX command line utilities have been my friend here. The following will trim the first line off the file. Just up the number to trim more lines off. I’m using Cygwin on Windows to run these.

sed '1d' file.txt # trims the line then replaces file.txt, so be careful.

awk 'FNR&gt;1{print}' file.txt &gt; newfile.txt # I ended up using this to be safe. It was easier to test.

tail -n+2 /path/to/file &gt; /path/to/output # can be tested with echo -e "foo\nbar\nbaz" | tail -n+2

Also in my case, I wanted column headers left in file, and thankfully the headers were pretty much the same across all the files. So I grep searched in the directory with all the files for the name of the first uniquely worded column and asked grep to also return the line numbers of where it found that word. Then I use that number (minus one) to put into my header deleting command.

&gt; grep -n 'Institution' *
HerpnetUtahBottomHalfProviders_January25_2010.txt:35:Institution	Collection	Catalog number text ...
HerpnetUtahTopHalfProviders_January25_2010.txt:27:Institution	Collection	Catalog number text...

Then, for example, I would use

 awk 'FNR&gt;34{print}' HerpnetUtahBottomHalfProviders_January25_2010.txt &gt; HerpnetUtahBottomHalfProviders_January25_2010_trimmed.txt

I love using Excel to craft a slew of commands then copy/paste them into the shell to batch process tens of files in my case.

EPA Supports WATERS Geospatial data through OGC WMS/WFS Services

Posted on by
Reply

EPA appears to be using ESRI’s ArcIMS 9.2 OGC connectors to publish OGC WMS/WFS services of their WATERS data.

See below for threads
that mention the limitations and issues with working with these semi-standard
services from ESRI.

http://mailman-viper.python-hosting.com/pipermail/users/2006-July/000103.html

http://openlayers.org/pipermail/users/2007-May/001571.html

http://www.mail-archive.com/users@openlayers.org/msg04240.html
- Little thread that I started a while ago to check on support for pure ESRI
services.

When you need a sexy terrain relief map

Posted on by
Reply

If you’ve ever needed to make nice looking relief maps like I do now, have a look at these sites.

http://www.reliefshading.com/ and http://www.shadedrelief.com/

I want to make something along the lines of Google’s terrain map, but with a client’s higher-resolution DEM data. I don’t if ESRI’s Spatial Analyst is going to get me quite where I want to go, so I’m going to have to maybe try some other tools

Good review of Mapnik for generating online maps

Posted on by
Reply

In my opinion you can’t beat UMN Mapserver or Mapnik for making gorgeous online maps. Mapserver has installed and run for me flawlessly every time I’ve tried it — without days of arguing with it. Mapnik has taken me nearly a week to get running sometimes. But, just like you wouldn’t want to shovel snow with a pitch fork, — always use the right tool for the right job. Mapnik is a focused tool for quickly turning geographic data (think shapefiles) into good looking static images (tiles) for serving to modern internet tiling maps.

If you need something like this (and a few other things) then read this excellent article.

http://mike.teczno.com/notes/mapnik.html

Plus it seems to be a good blog to check out from time to time.

History of Rasters in PostGIS and a glimpse into the mechanics of an Open Source Project

Posted on by
Reply

I love this post thread so much I’m going to stick it into my blog. First because if you read into it and follow the links you can see the history of raster support in PostGIS (or reasons for lack thereof) for the PostGIS newbie readers. You can also see Paul’s encouragement and explanation of how this open source project works. Open Source developers develop stuff for their own needs. However, if you can make a well educated and clear argument for why they should spend their time putting your functionality into the project, they might just do it. Otherwise, they usually say, “it’s open source. code it yourself.”

——- 

----- Original Message ----From: Paul Ramsey 
To: Pierre Racine 
Cc: warmerdam@pobox.com; nicolas.gignac@msp.gouv.qc.ca; smarshall@wsi.com; PostGIS Users Discussion 
Sent: Monday, July 14, 2008 10:11:05 PMSubject: Re: [postgis-users] PostGIS WKT Raster

Pierre,

Firstly, let me commend you for your understanding of the open sourcecommunity process! Not being shy, not taking "no" for an answer, andneedling the people you need to move are all excellent ways to keepthe wheels turning, particularly when done with good humor as you haveshown.  Other first timers could/should learn from your example!

So now I owe you a reply, no doubt:

You have gotten over my first hurdle, in that you have an actual usecase for raster, that is more involved than "I want to my images,because I hear database are faster".

I have long thought that analysis, in particular a fusing of vectorand raster analysis was the only really compelling use case for rasterin database, so again, you're on the side of the angels (me).

So, is your *design* good?

Probably as good as possible, but let me point out places of concernand see what you think:

- You propose to do this because their is a "great demand for it", butthe great demand is generally the stupid demand for images-in-database"just because". You must either somehow *stop* those people, or ensureyour solution is capable of meeting their needs.  Since you propose tomeet the demand, presumably you aim for the latter.  This *will*involve a good deal of non-analytical pre-processing infrastructure toconvert people's multi-resolution, overlapping/underlapping filelibraries into a uniform resolution coverage.  I suggest you ignorethese people.- You are going to carry a certain amount of information into thedatabase and process data into bands, potentially for externalstorage.  Why not store external data in a format like TIFF that canhold all the bands and pyramids, etc?- Once you have the idea of external storage, you could as easily moveto internal storage with the BLOB interface.  Not that I recommendthis, it's easier to back up and restore a filesystem of files than ahuge database full of BLOBs.- If you step back a bit and don't even bother splitting up therasters into tiles, you can use an existing raster access library likeGDAL to work with serialized data.- Or you could use GRASS as your serialization format and hook intothat.  Added bonus: free algorithms.- Basically the less you muck with creating a "disk format for raster"(solved problem) and the more you muck with "integrating raster andvector analysis in SQL" (unsolved problem) the more leverage you willget.- I like your external storage idea. What are the implications forCREATE TABLE foo AS SELECT...?

Summary: your proposal is better than any I have seen, addressessolving problems that if solved will provide actual new functionalityand benefit to users, and clearly you've thought this through oversome time.

So, I look forward to seeing your initial plan of attack for development  :)  

As you begin looking at the code base, you'll find a few stubs ofraster support that Sandro built at my request for rasterizing vectorsinto CHIPs in the database... basically the first tip toe down thepath you have written up so completely.

Go with God.

Paul



On Mon, Jul 14, 2008 at 12:43 PM, Pierre Racine wrote:

 Paul Ramsey hasn't yet said my design was:

 -a "meme" (http://blog.cleverelephant.ca/2008/06/x-my-l.html),

 -a "waste of time" (http://postgis.refractions.net/pipermail/postgis-devel/2007-July/002653.html)

-"useless" (http://postgis.refractions.net/pipermail/postgis-users/2007-May/015578.html)

 -that it was "pointless" (http://postgis.refractions.net/pipermail/postgis-users/2007-October/017250.html)

 -or that I was a "fool" (http://postgis.refractions.net/pipermail/postgis-users/2007-October/017239.html)

So I conclude it's a good design!!!  :-)  

 He asked for a good design some time ago for raster in the database (http://postgis.refractions.net/pipermail/postgis-users/2006-October/013628.html)

 Where is the clever elephant?

 Pierre

—–Message d’origine—–

De : postgis-users-bounces@postgis.refractions.net

[mailto:postgis-users-bounces@postgis.refractions.net] De la

part de Pierre Racine

Envoyé : 7 juillet 2008 11:30

À : postgis-users@postgis.refractions.net

Cc : warmerdam@pobox.com; nicolas.gignac@msp.gouv.qc.ca;

smarshall@wsi.com

Objet : [postgis-users] PostGIS WKT Raster

Hi raster people,

I’m starting a 2-3 year project to develop a web-based application to

automate certain GIS tasks commonly needed in ecological research. The

tool will support queries over VERY large extent based on a Canada-wide

assemblages of raster and vector data layers. The queries will

typically

involve intersecting those layers with buffers defined around points or

transects.

We would like to implement this system with PostGIS, but it currently

does not to support raster data. We could convert all our data to a

single format (raster or vector) and use other tools, however PostGIS

seems to us the best and most powerful vector analysis tool available

and we would prefer to use it. We would like to develop a unified

toolkit so that the application mostly need not worry about

whether base

layers are in vector or raster format. We are then strong proponents of

having raster functionality in PostGIS. I have reviewed every thread on

this list on the subject. I have analysed them and I have compiled them

in the wiki

(http://postgis.refractions.net/support/wiki/index.php?RasterNotes).

The argument goes like this: The geodatabase paradigm has been a major

recent enhancement in GIS technology, I feel that the seamless

integration of raster and vector analysis should be one of the next.

Spatial analysis is emerging, beside the making and publishing of maps,

as one of the main desktop and web-GIS applications. Rastor/vector

seamless integration is already done for display (in most GIS and with

MapServer or ArcIMS on the web) but definitely not for

analysis. Desktop

analysts must still learn to use two distinct toolsets within most

(all?) GIS packages: one toolset for raster and another for

vector data.

Would not it be easier to build and use applications if we had a unique

data query and analysis toolset independent of the data model? I don’t

know any tool having this approach right now. A PostGIS foundation that

addressed this problem would offer better directions to application

developers than the dichotomic one proposed by ESRI since their

beginning. It would provide the necessary abstraction to develop GIS

with ONE set of analysis tools. I feel this is one good reason to

integrate raster in PostGIS. There is “GIS” in the word “PostGIS”.

PostGIS should then provide a COMPLETE GIS data foundation (read

“base”!) for geoapplication developers. I think Steve Marshall’s design

is a good step in this direction

(http://postgis.refractions.net/pipermail/postgis-users/2006-De

cember/01

4059.html).

At the same time, we would have a chance to reconsider the raster model

as a coverage instead of a series of images. Spatial databases got rid

of map sheets by allowing complete vector coverages to be stored as

single table (e.g. the entire United States) This approach should works

for raster data as well. Isn’t this the approach taken by ArcSDE?

In summary, I think we must stop thinking about PostGIS as a

mere vector

data repository to support mapping applications. This is the way most

objectors to raster integration seem to view it. We must think about

PostGIS as a powerfull indexed data analysis tool. Seamless

raster/vector analysis in the database could lead to a major

simplification of geoapplications.

I prepared a PowerPoint presentation with a complete specification of

raster in PostGIS and an analysis of what should be the result of

overlay operation between raster and vector layers. You can download

this PPT at: http://www.cef-cfr.ca/uploads/Membres/WKTRasterPostGIS.ppt

Please have a look at it, feel free to answer questions I ask and

comment.

Here are the main propositions of this design:

-Like a vector feature, rasters are stored as a subtype of “geometry”(a

new WKB/WKT geometry type called RASTER instead of POINT or POLYGON)

-There is no distinction between a raster and a tile. A tile

is a raster

and a table of rasters can be seen as a tile coverage. Hence, contrary

to Oracle GeoRaster, only one table is needed to store a coverage and

there is only one type.

-It support raster in the database AND raster out of the database. This

mailing list has shown that both approaches have their pros and cons.

Let’s not impose one approach over the other.

-It supports: multi-band, pyramids and variable nodata value, raster

size, pixel size and pixel type for each row.

-It supports import/export from/to Tiff and JPEG.

But moreover seamless raster/vector integration is materialized by

theses propositions:

-A lot of existing vector-only functions are adapted to work

with raster

also.

-Most new raster functions are also implemented to work with vector.

Only basic raster derivation functions are proposed.

-Most vector/vector existing functions are adapted to work seamlessly

with raster/vector or raster/raster

I also want to ask:

-What is the status of PGRaster? Is any development now underway?

We have some resources to devote to this project over the next

few years

and are very interested in forming collaborations to move this work

forward.

Pierre Racine

GIS/Programmer Analyst

University Laval

http://www.cef-cfr.ca/index.php?n=Membres.PierreRacine

Putting your own content into a website with Google Maps

Posted on by
Reply

So I’m doing a little research tonight on some requirements for a client:

1. Embed a map in the client’s home page (non-database driven ASP.Net which is essentially HTML) that depicts the county’s watersheds and some number of additional layers
2. Let users click each watershed polygon to trigger an event that leads to them learning more about the conditions within the polygon (think old school image maps). This could be as simple as take the user to a static HTML page about the watershed.

The client currently supports ArcIMS (v9.1) and Google Maps. There is no immediate plan to move to ArcGIS server, though we will learn more next week.

I think that embedding ESRI in their pages for such a simple need is overkill and the client has already won awards for their use of Google Maps. So I’m researching options to use the Google Maps API and Google Gadgets to meet their needs

Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

Posted on by
Reply

http://www.bostongis.com/PrinterFriendly.aspx?content_name =sqlserver2008_postgis_mysql_compare

From the PostGIS Listserv comes a very useful comparison table that has been partially vetted by the members of the list. I just wish they included Oracle Spatial and Oracle Locator.

Paragon wrote:
/> As part of our due diligence work, we are formulating a comparison of the
/> spatial functionality offered by SQL Server 2008 Spatial, PostGIS and MySQL.
/> Its currently in draft, but we hope to release within the next week.
/>
/> We would appreciate if people could take a quick look at it and let us know
/> if you see any gross errors or important things we left out. Most of this
/> information we curled from reading the help docs of each product and from
/> our general familiarity with the databases and GIS products out there. We
/> tried to be as fair as possible.
/>
/>
/> http://www.bostongis.com/PrinterFriendly.aspx?content_name=sqlserver2008_pos
/> tgis_mysql_compare
/>
/> Thanks,
/> Regina and Leo