Load SHP Files, DrawPolygon, select features/points in Polygon... return data as table

Sep 12, 2010 at 4:35 PM

Hi All

I want to be able to do the following with a map with two layers (Layer1 = SHAPEfile, Layer2 is a set of X,Y point data from MSAccess Database or MSSQL)....

1.  Draw a polygon on the map

2.  Select the points (from Layer2) that are within that polygon

3.  Return a FeatureDataset/table... giving point X,Y coords and other data from underlying MSAccess db) for those selected points.

 

I have been trying this using SharpMap and worndered if DotSpatial has a simple way to do this?

 

Cheers

James

Developer
Sep 13, 2010 at 2:47 AM

You can look at (or even use) the ShapeEditorApp as an example of how to draw content to the map, as well as saving the created polygon as a shapefile.  It's complex enough code that the forum probably is not a great spot for it, but most of what you want could be copied almost verbatum from the polygon part of that code.  Most of the code you want is in the "AddShapeFunction.cs" class.  You don't necessarilly have to work as an App, since you can create a new "MapFunction" and add it to the map Manually.

Testing to see if a point intersects with a polygon can be done at multiple levels.  It can be done either with two feature classes, one that represents a point and one that represents a polygon, or you can actually work with the geometries if you are working with code.  DotSpatial.Topology supports the geometry classes in the DotSpatial.Geometries namespace.  The ones that are important in this case are Polygon and Point.  Then you can use the "Intersects" method for point in polygon testing.

To host the content from MicrosoftAccess, you can use an OleDB Data Adapter in order to populate a System.Data.DataTable with the values.  If your database is small, you can host all the content you want in the FeatureSet.DataTable for all the points and then use the myFeatureSet.Feature[index].Intersects(PolygonFeature) methodology to select the feature you want, attributes and all.  If your database is very large, then I recommend hosting just the vector points along with a single indexed field from the database (like FID or whatever) so that after doing your intersection test, you can query the database for the desired attributes.  The return FeatureSet could then be devised by adding the features from the original dataset, but then simply using the myFeatureSet.DataTable.Columns.Add(newName, newType) approach to get your schema set up and append in the rest of the information.

Here is some related sample code that might help:

http://dotspatial.codeplex.com/wikipage?title=Desktop_SampleCode&referringTitle=System.Spatial.Data

 

Sep 13, 2010 at 3:01 PM

Thanks Shade1974

 

Maybe I have a few issues here.  I am trying to get this all to work in VS Express 2010 and I am not a native C# programmer (mainly Delphi/Pascal).  I have also been trying out a sister project "SharpMap" with some success but always I have the selecting points problem.

 

I am still battling on with this until it finally sinks in!  In the meantime has anyone generated easy to use code for this sort of problem?  Is there a demo project with code (winforms) which I can play with?

 

Sorry again for my ignorance... perhaps I am trying to run before I can walk!

 

Very best regards

James

Developer
Sep 13, 2010 at 3:26 PM

Are you using C# in VS 2010 or a different language.  (I am not aware of VS 2010 supporting Delphi or Pascal).  If I know what language you are using, I can probably whip up some sample code for you.

 

Sep 13, 2010 at 3:38 PM

Indeed I am using C# in VS 2010 (I am also using Delphi PRISM which also runs in VS 2010 shell).  Both the C#.NET and Prism in VS 2010 shell allow me full access to .NET framework up to and including 4.0... hence I can import and use all the DotSpatial objects/DLLs.

I am using an MSAccess table/file with about 180 records with around 10 fields - including X and Y coordinate fields (as Double)...

 

So if you can "whip up some code" that allows me to put the X,Y points from the access file on a map then allow me to draw a polygon and select the points inside and dump the selection to a table then you are a true gentleman, Sir!

 

Very best regards

James

Developer
Sep 13, 2010 at 10:05 PM

I have added a zip file containing an example I wrote. that does what you want.  The biggest problem I had is that I have discovered a bug.  For whatever reason, it isn't initializing the vertices properly when I created the point layer.  I had to call "InitializeVertices" manually.  This is a bug and not by design, but for now this code should work for you.  Just go to the latest download on the downloads page and look for AccessExample.zip.

Sep 14, 2010 at 11:30 AM

Thanks again you are a legend.

 

Unfortunately (you knew I was going to say that) I get a runtime error... "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."  Basically I am still using Access 2003... I guess I need Access 2007 or 2010 installed?

 

Cheers

James

Sep 14, 2010 at 11:58 AM

shade1974

 

I downloaded the Access 2007 data access components from MS site now all is fine.

 

I cannot thank you enough for your help... pure brilliance!

 

Cheers

James

Sep 14, 2010 at 4:18 PM

Do you have a version with an Access 2003 or earlier MDB?

 

Cheers

James

Developer
Sep 14, 2010 at 5:02 PM
Edited Sep 14, 2010 at 5:03 PM

On the left hand side of your development screen you should see an expandable window called "Data Sources".  Expand that and then click on the first icon for "Add New Data Source".  Choose "Database" then click [Next>].  Choose DataSet, then [Next>].  Press the "New Connection..." button.  Choose Microsoft Access Database File.  I also remove the check from the box that says "Always use this selection".  (Not sure what it does, but I'd like to continue to be able to choose between Oracle, SQL Server, ODBC, or Access in the future).  Press Continue.  Browse for your 2003 mdb database.  You may or may not have to enter user name and password, depending on how the database is set up.  Press "Test Connection" to make sure it works.  Then press Ok.

I seem to also remember, somewhere in there, the choice for what to name the newly created dataset as well as getting to choose whether to bring in view, tables or both and getting to use checkboxes to pick them.  Setting up the database connection in this case should be pretty straight forward since you just sort of click through the wizard and it sets things up for you.  You can set the datasource property on the data grid view to the table in order to make sure that the schema for the DataGridView is updated to work with your mdb table instead of the one I used.  The important part of the code that I wrote doesn't care how you get the data table in the dataset, as long as you have one populated.

If your table is too large to fit in memory we may have to work out something else, but if you are working with Access, the data table is probably small enough to fit into memory.

Ted

 

Sep 15, 2010 at 8:58 AM

Hi Ted

It doesn't want to work with my table in my MDB.  If followed your instructions and I can see my table in the project.  I can make the dataGridView1 point at my table.  When I run/debug the app it seems to go back to your database table.  Seems that everything points back to the "myDatSet".  Any more clues?

 

In an ideal world It might be useful for me to allow the end user to pick an access file which then connects up... code like... the SharpMap code below...

//=============================

string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=MyDatabase.mdb"; //Connectionstring
string tablename = "pointsTable"; //Name of table in database
string oid = "ID"; //Name of object ID column - MUST be integer!
string xColumn = "X"; //Name of X coordinate column - MUST be double!
string yColumn = "Y"; //Name of Y coordinate column - MUST be double!

SharpMap.Map myMap = new SharpMap.Map(new System.Drawing.Size(500,250)); //Initialize map object
SharpMap.Layers.VectorLayer layAddresses = new SharpMap.Layers.VectorLayer("Addresses"); //Create layer
layAddresses.DataSource = new SharpMap.Providers.OleDbPoint(connStr, tablename, oid, xColumn, yColumn); //Set the datasource to the database
myMap.Layers.Add(layAddresses); //Add layer to map

myMap.ZoomToExtents(); //Zoom to extents
Image img = myMap.GetMap(); //Render map
//=============================

Cheers
James

Developer
Sep 15, 2010 at 3:17 PM

Sorry, I should have mentioned this, but line 33 in the "SetupPoints" method of the Form1 class:

             _fs.CopyTableSchema(myDataSet.Locations);

This might seem like gibberish, but this is where I am ensuring that the shapefile learns about the attributes.  Change "MyDataSet.Locations" to whatever your dataset and table name are.

Also, double check line 40:

 Coordinate c = new Coordinate(row.Longitude, row.Latitude);

The above line needs to be written so that the appropriate column name affects your own system.

There is a possibility of setting up something like what they did, but this is in addition to supporting SQL Spatial and other things as well.  Most of that can be done with a little extra code right now, but you are correct, we could definitely make it a little easier on the user.

 

Sep 15, 2010 at 6:23 PM

Thanks again... maybe getting closer...

 

I changed the code thus...

//===================================

   private void SetupPoints()
        {
            // Set up a featureset for ALL points, not just the ones we want to export or whatever.
            _fs = new FeatureSet(FeatureTypes.Point);
            _fs.CopyTableSchema(liverpoolRanksDataSet.tblRankSurvey);
           
            // This example tries to keep it simple by simply grabbing all the schema into our original featurest
            foreach (MyDataSet.LocationsRow row in liverpoolRanksDataSet.tblRankSurvey)
            {   
                Coordinate c = new Coordinate(row.Latitude, row.Longitude);
                IFeature pointFeature = new Feature(new Point(c));

//===================================

This seems to hang/error at line 36 "foreach"

The data structure is this...

RankRefNo RankName ExactSiteLocation X Y
1 Beaumont Street (north west side) From a point 13.7m south west of its junction with Lodge Lane in a south westerly direction for a distance of 20m 336917.118 389396.321
2 Beech Street (south west side) From a point 7.5m south of its junction with Kensington in a south easterly direction for a distance of 20m 337332.782 390989.668
3 Belmont Road (northwest side) From a point 15m northwest of its junction with Belmont Grove in a north westerly direction for a distance of 20m 336994.959 392178.543
4 Berkley Street (north east side) From a point 15m south of junction with Upper Parliament Street in a southerly direction for a distance of 16.2m (rear wheels to kerb) 335744.901 389198.784
5 Bold Street (northeast side) From a point 13.7 m southeast of its junction with Newington in a south easterly direction for a distance of 10m 335034.941 390047.61
6 Broad Lane (north side) From a point 2m west of the boundary line between No. 58 and 60 Broad Lane produced in a westerly direction for 30m 338478.212 394166.837

Should I rename my X and Y columns to Longitude and Latitude?  The X and Y are to the UK national grid coordinates.

 

Cheers

James

 

Developer
Sep 15, 2010 at 8:11 PM

Change "MyDataSet.LocationsRow" to liverpoolRanksDataSet.tblRankSurveyRow

Change "Row" and "Column" properties on the redimensioned row variable to match your X and Y values.

 

Sep 16, 2010 at 9:15 AM

I have tried what you suggest but get the following error

Error    1    An object reference is required for the non-static field, method, or property 'AccessExample.LiverpoolRanksDataSet.tblRankSurvey.get'    C:\NonBackup\DotSpatial\AccessExample\AccessExample\AccessExample\Form1.cs    33    33    AccessExample

 

The code highlights errors in lines 33 and 36 thus...

//=======================================

        private void SetupPoints()
        {
            // Set up a featureset for ALL points, not just the ones we want to export or whatever.
            _fs = new FeatureSet(FeatureTypes.Point);
            _fs.CopyTableSchema(LiverpoolRanksDataSet.tblRankSurvey);
           
            // This example tries to keep it simple by simply grabbing all the schema into our original featurest
            foreach (LiverpoolRanksDataSet.tblRankSurveyRow row in LiverpoolRanksDataSet.tblRankSurvey)
            {
                // This is where VS2010 rocks!  You can use strong typed properties on row instead of having to
                // tool around looking for the correct object and hoping it survives a cast etc.
                Coordinate c = new Coordinate(row.X, row.Y);
                IFeature pointFeature = new Feature(new Point(c));

//=======================================

 

all else seems fine.

 

Any ideas... I expect it is simple but I may be simpler!

Cheers

James

 

 

Developer
Sep 16, 2010 at 4:16 PM

Ack, when you update the CopyTableSchema and gave me the value, I had assumed you were copying the schema from the object instance.  In my case, the only distinction between the two is case.  So the class might be LiverpoolRanksDataSet, but you should have an instance of the dataset that is lower case.  I have highlighted the case usage that should probably exist in your case.

// Set up a featureset for ALL points, not just the ones we want to export or whatever.
            _fs = new FeatureSet(FeatureTypes.Point);
            _fs.CopyTableSchema(liverpoolRanksDataSet.tblRankSurvey);
           
            // This example tries to keep it simple by simply grabbing all the schema into our original featurest
            foreach (LiverpoolRanksDataSet.tblRankSurveyRow row in liverpoolRanksDataSet.tblRankSurvey)
            {
                // This is where VS2010 rocks!  You can use strong typed properties on row instead of having to

Sorry about the confusion.  The only Liverpool that should be Cyan in your debugger is the one I highlighted blue here.  The other liverpool dataset references should be black.

 

 

 

Sep 16, 2010 at 4:30 PM

Brilliant!  That seems to have sorted it for now.  Just need to work out adding a SHP file layer and then making it flexible for the end user to choose their own MDB points data and relevant SHP file(s)...

 

Thanks a zillion and thanks for your patience.

 

My very best regards

James

Feb 11, 2014 at 1:38 PM
hi guys!
somebody has that archive AccessExample.zip
If not, maybe someone can tell me how to open the ESRI Access Geodatabase (mdb)?
I will be very grateful!!

P.S. Sorry for my English, i am ukrainian and still badly know English..
Feb 11, 2014 at 2:27 PM
Hi Alex

Your command of English is totally brilliant compared with my total lack of Ukrainian!

I believe I was the the one who started the original thread.

Please send me your personal email address and I will send you a link to download the file.

I hope this helps

Cheers
James

Feb 12, 2014 at 2:29 PM
Hi Alex

I have uploaded the file to "SOURCE CODE > PATCHES" with ID=15848

HTH

James
Mar 5, 2014 at 1:56 PM
Hi James
I apologize for my absence is due to the political situation in our country ..
I am very grateful to you for your reply ..
I downloaded the file and now will be work with it..
thanks..

Alex
Mar 5, 2014 at 4:17 PM

Alex

No problem. I hope you and those you care about are safe.

Best regards
James.

Mar 7, 2014 at 7:46 AM
Edited Mar 7, 2014 at 7:47 AM
Hi James
I took apart code from the archive AccessExample.zip and saw a simple example of how you can use a database to store features .. but at the moment I am interested how to open and read data from Esri Access geodatabase (mdb) that was saved using ESRI ArcGis ... do you have such an experience, and if there is something I am very grateful to you if you will tell me which way to move ..
Thanks, Alex
Mar 7, 2014 at 9:55 AM
Hi Alex

I see what you are trying to do. Unfortunately I have not used the ESRI ArcGIS MDB as a store. I only had point data in a straightforward MS Access DB table (i.e. X and Y coordinates and fields for ID and name, etc). i think if you have ArcGIS you can convert the access mdb to either a File GeoDatabase or dump everything to SHP files.

I know that SharpMap (http://sharpmap.codeplex.com/) has support for some ESRI file databases - perhaps that might be useful?

Another option could be to use Postgres/PostGIS as your data store... very fast and elegant + lots of tools for import/export of data from SHP, DXF, etc. i think you can find ESRI MDB / FileGDB import utilities too.

Hope this is helpful.

Best regards
James


Mar 7, 2014 at 2:56 PM
I can use ArcGis but one of the tasks of my software to perform reading of ESRI Geodatabase, that it would not fit .. and sharpmap also not an option .. I've seen what MapWindow can do it, then dotspatial why not? ..
thanks for the reply ..