Cold Fusion with SQL and Geo Spatial Mapping Geo Fence Functionality

[rai...@ozemail.com.au]

I have the Tracking Server with listener set up so the listener captures the data (long / lat) sent from the mobile tracking devices to the server, which is then stored into an sql table

 

I have the Bing Maps API set up so the locations stored in the above database table correctly display on the bing map

 

The challenge is trying to figure out how to create Geo Fence functionality combining SQL 2008, using Bing Maps API and using CF application to manage and display the Geo Fences

 

I am wanting to be able to create circular zones so the co ordinates are saved in the geo fence table assigned to a specific device id,

 

Once the geo fences are created and stored in the geo fence table each new location sent to the tracking server then needs to be validated against existing geo fences to determine if the new location is inside or outside of geo fences listed in the geo fence table

 

If anyone can assist to direct me to a resource or example code that deal with the above I would appreciate the advise

 

 

Kind Regards

 

Claude Raiola

[Simon Haddon]

I am not sure what the Geo Fence table actually is but either way to find if a location is inside or outside your fence it is simple if you have a closed polygon. You are looking to performa point-in-polygon test where an even result means the location is outside and an odd result means it is inside. Have a look at http://en.wikipedia.org/wiki/Point_in_polygon and in particular the ray-casting method.  I have used this before and it is fast.

Cheers,
Simon

--
You received this message because you are subscribed to the Google Groups "cfaussie" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cfaussie+u...@googlegroups.com.
To post to this group, send email to cfau...@googlegroups.com.
Visit this group at http://groups.google.com/group/cfaussie?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Cheers
Simon Haddon

Woman loves feeling danger and speed. That is why woman wants man.  They get a speed rush that is the most dangerous of all.

[rai...@ozemail.com.au]

Hi Simon

 

A GeoFence Table is a table that list all the GeoFences that have been created each record represents a different geo fence each with its own set of co ordinates that make up the GeoFence area

 

Thanks for your response I will look into the reference you have provided

 

 

 

Kind Regards

 

Claude Raiola

SAMARIS Software

Call 1300 255 990

[Simon Haddon]

Ah, I took my own advise as to what a GeoFence is. It is a virtual boundary. Makes more sense now. I thought you might have been mapping real boundaries. Either way. I have some code I wrote in C years ago somewhere that does a point-in-polygon check. It was easy. The main trick is to shift the coordinates so are all positive numbers. It probably doesn't matter for longitude as you would have 0-360 but shift any latitude numbers up if any are negative. Then you can easily extrapolate if either the x or y axis are crossed a positive or negative number of times. It also handles geographic donuts if you have any.

Another option might be to move your data into PostgreSQL with PostGIS extensions. Then you have all the geospatial tools you need .

Cheers,
Simon

[Robin Hilliard]

Hi Claude,

Funny, happened to have some CF code to check for polygon containment open when I saw your post:

function polyContains(poly, lon, lat) {

    var j = 1;

    var oddNodes = false;

    var lenPoly = arrayLen(poly);

   

for (var i = 1; i < lenPoly; i  += 2) {

j  = i + 2;

if (j > lenPoly - 2)

j = 1;

if (((poly[i + 1] > lat) != (poly[j + 1] > lat)) // the polygon edge passes through the lattitude lat

and ((((lat - poly[i + 1]) * (poly[j] - poly[i])) / (poly[j + 1] - poly[i + 1])) < lon)) // at lattitude lat, polygon edge is west of lon

oddNodes = !oddNodes;

}

return oddNodes;

};

I'm using this because MySQL spatial polygon containment only compares bounding boxes (on the version supported by Redhat). MySQL whittles the list down to a few polygons and I use this to do the rest, based on the odd/even polygon containment test. Note that poly is a flat array containing lon lat pairs. 

I'm currently running some tests on a database of about 5,000 polygons covering SE Australia, sampling every one km grid. There are a few anomalies I'm investigating, hopefully it's the data and not an algorithm glitch but you have been warned.

Robin

	ROBIN HILLIARD Chief Technology Officer ro...@rocketboots.com.au RocketBoots Pty Ltd Level 11 189 Kent Street Sydney NSW 2001 Australia Phone +61 2 9323 2507 Facsimile +61 2 9323 2501 Mobile +61 418 414 341 www.rocketboots.com.au

[Zac Spitzer]

SQL Server 2008 also supports proper spatial?

--
Zac Spitzer
Solution Architect / Director
Ennoble Consultancy Australia
+61 405 847 168

[rai...@ozemail.com.au]

Hi Zac

 

Thanks for your reply

 

If you have any suggestions on the best way to record geo fence zones with z radius so that the system can then detect whenever a given reported location is inside / outside of a given zone I would appreciate it

 

I am using the Bing mapping api and I am developing the tracking portal app in CF

 

 

Kind Regards

 

Claude Raiola

SAMARIS Software

Call 1300 255 990

 

From: cfau...@googlegroups.com [mailto:cfau...@googlegroups.com] On Behalf Of Zac Spitzer
Sent: Wednesday, 5 June 2013 3:07 PM
To: CFAussie
Subject: Re: [cfaussie] Cold Fusion with SQL and Geo Spatial Mapping Geo Fence Functionality

 

SQL Server 2008 also supports proper spatial?

[Zac Spitzer]

you just need to create a table with the polygons, then you can use a built in
function like STContains

http://technet.microsoft.com/en-us/library/bb933904.aspx

also, I would recommend considering avoiding using propriety
vendor apis like google maps or bing, stick with something like flexible like
openlayers or leafletJS so that you can easily swap backends

[rai...@ozemail.com.au]

Are your concerns re vendors api's only to give you the flexibility of changing backend at some later date with less hassle

Also the STContains is that something I can use within my CF query, only every written very standard cf queries in the past so excuse me for asking the question if it appear a little novice

[Zac Spitzer]

re vendors api's yes... also be aware of the strict usage and licensing restrictions with those vendor api's/services

using STContains is really simple, it just goes in the where clause and returns a normal query,

if you are returning the spatial objects use .STAsText() to return WKT which you can then
pass into your javascript stuff

[rai...@ozemail.com.au]

Hi Zack

 

Thanks for that

 

But if I do not use Bing or Google apis with their associated maps where do I source the mapping information from and also have confidence that the mapping information / reverse geo coding results etc are accurate and kept up to date

 

The entry level Bing mapping license for fixed assets for example is around $6,000 pa where as the Google equivalent is closer to $12,000 pa