How to store MapInfo data in SQL Server, then query from another app

[Anthony]

I am somewhat new to MapInfo, so would appreciate any suggestions or
pointers to other posts in this forum that address this question.

We have MapInfo files that contain "signal strength data" for our
system. We need to create another application (in .NET) that can
lookup the data value for a given location (i.e. a latitude/
longitude). I had considered exporting the MapInfo data to GeoTIFF,
then reading from those files, which seemed to work ok (I used the
GDAL library to read the GeoTIFF file).

Another approach that we are considering is to store (or export) the
data in SQL Server, then do native SQL queries to access the data. Can
someone provide an overview of how MapInfo stores its data in SQL
Server, and how one would query that data to find a data value at a
particular latitude/longitude? (We would likely be using MapInfo 10
and SQL Server 2008).

I apologize if this question has been asked, but I couldn't find it in
this forum. If so, can you point me to the relevant posts.

Thanks.
Anthony

[Mike Osbourn]

Anthony,

Although I can't give you an "under the hood" type answer for how MI stores
it's data in sql I can say that we have been using sql server 2000 and
spatialware for some time now. Much of our data is accessible from various
applications that can consume data via an odbc link or some other method to
sql. Some examples are MS Access, excel and others. Just remember that the
geographic data itself from sql 2000 backwards is generally referenced via a
map interface or inside some tsql commands. That being said, I have
tinkered a bit with sql server 2008 and MI Pro 10 using the native
geographical data storing capabilities. It seems to work well but you might
want to do some reading on the two methods of storing data. Spherical and
planer (I always seem to get confused between the correct terms geographic
vs geography). I haven't had the time to do much on the spatial functions
of sql 08 using tsql type interaction but you should be able to interact
with you data via a form based interface and do some spatial type queries.
It's a really overlooked method of interacting with spatial data IMO.

I'm pretty sure the sql 08 has a good deal of spatial functions built in to
it but don't have a list that I can share. You could either "bing" or
google it I'm sure. Do a search on spatial ed which is a blog by one of the
MS Product managers I believe.

Anyway, Hope this helps.

Mike Osbourn

[Anthony]

Mike, thanks for the reply.

So, it seems that if I can store my data in SQL Server 2008 using the
new geospatial data types ('geometry' and 'geography'), then I don't
need SpatialWare, and should be able to access (and update) that data
directly from MapInfo 10. Also, with the data stored in SQL, then I
can just use the native SQL geospatial functions to query that data.
Are those reasonable assumptions about how to accomplish this?

Thanks again for any pointers.

[Mike Osbourn]

Yep basically you are right on track... I would say that there may be a few
issues (but take this with a grain of salt) on sharing saved tab files out
of a backend sql 08 table with previous releases of MI Pro. I'm still on
the beta of 10 so I can't say with any certainty that the version stamp with
the released product hasn't changed. In other words, Save a copy of a
backend sql 08 database as a native tab file and note the version number.
I'm pretty sure it will stamp it with 10.

What I would really love is the ability to consume a sql 08 spatial database
with any mapping client I so desire without any middleware, pixie dust or
other magical method:) I think Autodesk might be able to do this using some
FDO (I think that is the correct term) data drivers but I don't think the
"other" product can at this point without some middleware.

I'm also reasonably sure that either with this version or the next version
of sql 08 the reporting engine embedded will give you the ability to have
some routines where you can schedule one that does spatial functions and
have a map as part of the report. All inside sql.. Now that is really
"Cool"..

Make sure to read on Geometry vs. geography and the limitations of the data.
It can become rather painful in some situations from what I've read. We
tend to use geography since our data is not world wide.

I have been successful so far by downloading and installing the free version
of sql 08 (I think it's called sql express) and loading, editing and
exporting data directly from Mi Pro 10 beta. You should be able to give it
a try but remember that stored procedures etc I don't think are supported in
the express edition.


HTH

Mike

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On
Behalf Of Anthony
Sent: Friday, July 03, 2009 12:23 PM
To: MapInfo-L
Subject: [MI-L] Re: How to store MapInfo data in SQL Server, then query from
another app

[Pesic Nebojsa]

Hello Mike,

> I have been successful so far by downloading and installing the free version
> of sql 08 (I think it's called sql express) and loading, editing and
> exporting data directly from Mi Pro 10 beta. You should be able to give it
> a try but remember that stored procedures etc I don't think are supported in
> the express edition.

is it possible to store lines,polygons in SQL Express Edition 2008
directly ?
or its only possible in full version ?

--
Best regards,
Pesic Nebojsa

[Mike Osbourn]

Yes... With MI Pro 10 you can. You might want to read MS license regarding
personal or commercial use. I'm not real sure on the limitations of their
license. It may require you to install the sql 08 odbc client on your
machine if I remember right. I have done all this via an ODBC connection to
sql express.

I don't think (at least I haven't tried) you can store raster imagery on it
though. This may be a limitation of MI Pro and not sql 08. There is a data
type in sql 08 termed filestream which presents some real interesting
possibilities with other types of data. At least as far as I can tell. You
may want to do some reading on it.

HTH

Mike

-----Original Message-----
From: mapi...@googlegroups.com [mailto:mapi...@googlegroups.com] On
Behalf Of Pesic Nebojsa
Sent: Friday, July 03, 2009 1:58 PM
To: Mike Osbourn
Subject: [MI-L] Re: How to store MapInfo data in SQL Server, then query from
another app