PostgreSQL and SQL Server 2008 (DBI-Link)

Question

At work we are having a bit of trouble with spatial support of SQL Server 2008.

In SQL Server 2008 we have a big system in production going on, managing a bunch of important stuff. In some of the tables, I have pairs of coordinates and need to display them to ArcGIS and other GIS software.

My question here really is: Is it possible to use DBI-Link (PostgreSQL tool) to connect to SQL Server 2008?

What kind of performance loss should I expect? I don't expect to conduct complicated queries. It's just a matter of reading from PostgreSQL a view inside SQL Server 2008 (a simple view, such as SELECT * FROM foo).

So, what are you thoughts about this? I know this is a bit haxor solution, but inside SQL Server I lose a lot of spatial handling functions, and all my databases in SQL Server stores are coordinate pairs.

Answer 1

Yes, that should work fine, as long as you have a DBI driver properly set up.

Performance - depends on what you're doing. DBI-link doesn't have the ability to push down restrictions, so if your view is on "SELECT * FROM foo", it will always do that. If your app does "SELECT * FROM myview WHERE pk=1", it will still request the whole table with SELECT * and then filter it on the pg side. You may be better off using functions that can adapt the query.

As long as your queries don't shuffle lots of data, performance is usually pretty decent.