1 Topic by HarryClarke

  • HarryClarke
  • Veteran
  • Offline
  • From: Surrey
  • Registered: 11-10-2011
  • Posts: 99

Topic: Change backend database to PostgreSQL

It would be helpful if the backend database for Recorder 6 could be changed from SQL Server to PostgreSQL.
This should be relatively straight forward provided that Recorder 6 uses standard SQL commands and has not used Microsoft specific extensions.

I use GIS a lot to analyse the data, which means at present I have to export the data out of Recorder 6, and then import it into PostgreSQL/PostGIS and then run a script to convert Grid Reference into a spatial point that can be used by GIS software, such as QGIS. Theoretically this all could be done in SQL Server, which is fine if you can afford the commercial version of SQL Server with the Spatial option enabled. However, I use SQL Express which does not provide that functionality.

Another advantage is the cost saving for those organisations that need to use the multi-user version of Recorder 6. i.e. no longer need to buy commercial licences of MS SQL Server.

Harry

Harry Clarke
Surrey County Butterfly Recorder

2 Reply by AndyFoy

  • AndyFoy
  • Consultant
  • Offline
  • Registered: 09-08-2006
  • Posts: 219

Re: Change backend database to PostgreSQL

Harry

SQL Server Express does indeed support 'Geometry' and 'Geography' spatial fields - so long as you are using SQL Server Express 2008 Release 2 or later.  I've been using it for several years to 'spatialise' Recorder 6 data for viewing in both MapInfo and ArcGIS so I'd be surprised if you can't do it in QGIS too.

I don't know if SQL Server Express can support the network version of Recorder 6, but I don't see why not.  Can anyone confirm this?

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

3 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,620

Re: Change backend database to PostgreSQL

Sql express can be used on a network and is used by a number LRCs.  There are some limitations . The main one being on the maximum size of the database file. This was 4gb on SQL 2005, but  from 2008 on has been 10gb. This is enough to support a fairly large system.

There is some infiormation on using SQL Spatial fields  at http://forums.nbn.org.uk/viewtopic.php?pid=17111#p17111

It is fairly easy to create a table in R6 with the spatial fields and to use SQL or a Batch update to populate the table.  Jut how easy that would be to use in QGIS I am not sure.

Mike Weideli

MikeWeideli's Website