0

I have an Access table with venue information. I'm toying with nearest neighbor stuff to show the website viewer the upcoming event that's nearest to them.

The main, in-house, database is Access but the website pulls the data from MSSQL. What I currently do is maintain the Access database, export the table as Excel 2003, transfer .xls to the web server, delete the table and importing the .xls (within SQL Management Studio).

It is a laborious process and I just realized that, when I import the .xls, I will have to go through and reset all the spatial information (set a primary key, set the data type for lat/lng to geography, give it a spatial index).

Is there a way to automate this process? Is there a way to set the data types and keys during the import process? Obviously, the right thing to do is use MSSQL as the back-end and forgo all the work. Unfortunately, my superiors haven't been receptive to making the change.

Cœur
  • 37,241
  • 25
  • 195
  • 267
doubleJ
  • 1,190
  • 1
  • 16
  • 29
  • http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr You can call a function from MS Access, see last comment(s) – Fionnuala Jul 04 '12 at 16:37
  • Can you create an ODBC link in Access to the SQL Server table? – HansUp Jul 04 '12 at 16:59
  • @HansUp I haven't been able to connect to MSSQL remotely. The only way that I've been able to connect, thus-far, as been with Remote Desktop or a web page. That's why I have to do the whole export / transfer / import process. – doubleJ Jul 04 '12 at 17:08
  • @Remou I read through that, but it sounds more like they are using MSSQL as a back-end and manipulating that data with an Access front-end (that's what I would like to do, but I can't connect to MSSQL with Access, because of security policies). What I'm wanting to do is have lat/lng in the Access table and have them translate, properly, to MSSQL. It's possible that I could work with the script (the whole point of the page in question) for the import process, though. – doubleJ Jul 04 '12 at 17:10
  • What method do you use to transfer the .xls file to the server? FTP or something else? – HansUp Jul 04 '12 at 17:19
  • Too bad. Well at least you should be able to create a script to load the data into SQL Server and run it as a scheduled task. That way you needn't fiddle with a web page or SSMS from a remote desktop session. – HansUp Jul 04 '12 at 17:23

1 Answers1

0

I found some information on a page working with basic spatial data that shows you can have lat/lng as separate float fields and/or a POINT(lng,lat) geography field.

With this being said, I could just have a separate lat/lng fields in Access and they should import into MSSQL. By that page, I don't even require a geography field (float will work with some extra code. Assumingly, I should be able to populate the geography field from the float fields with a query.

doubleJ
  • 1,190
  • 1
  • 16
  • 29
  • According to [this page](http://www.sql-server-helper.com/sql-server-2008/convert-latitude-longitude-to-geography-point.aspx), you can do that conversion like this...`UPDATE [dbo].[Landmark] SET [GeoLocation] = geography::STPointFromText('POINT(' + CAST([Longitude] AS VARCHAR(20)) + ' ' + CAST([Latitude] AS VARCHAR(20)) + ')', 4326) GO` – doubleJ Jul 04 '12 at 18:06
  • 1
    If you're only doing queries such as approximate distance to stores/identifying those stores within x radius of a location, I'd stick with using separate float fields for latitude/longitude. The geography datatype is better suited for dealing with more complex spatial entities (linestrings, polygons etc.) or for more complex query types (e.g. tests of intersection, topological relationships etc.), but I'd say it adds a layer of unnecessary complexity in your case. – Alastair Aitchison Jul 04 '12 at 21:10