1

Re: PostGIS (PostgreSQL) or MySQL?

A quick call for opinions. When I finally make my foray into web-based mapping, which should I use and why? Or perhaps a better way to frame the question would be: what do you use, and what has your experience been like?

An important factor would be support. What is the community like surrounding each technology?

Cheers,
Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

2

Re: PostGIS (PostgreSQL) or MySQL?

Hi Charles

Becauase I use PHP as my preferred scripting language, MySql seems to be a natural partner and is frequently packaged up as such. MySql (like PostgreSQL) is a spatial database and I am just learning the full capability and some of the neat things to consider (e.g. use of spatial indexes for faster searching plus built in functionality).

I have also discovered a neat tool for converting Shapefile to MySql (Shp2Mysql), which means that using another neat tool (Navicat) I can shoot all the data into a MySql database. Using PHP I can then build a whole bunch of queries on that data. However, I guess that in principle this approach could also be implemented for PostgreSQL.

So it would come down to:
1) MySql features, which according to Wikipedia "implements the datatype geometry plus some spatial functions that haven't been implemented according to the OpenGIS specifications. Functions that test spatial relationships are limited to working with master bounding rectangles rather than the actual geometries."
2) Support for PHP/MySql seems good and lots of work going on there but seems to apply equally to PostgreSQL.

So my gut feeling is that there probably isn't a whole lot of difference - may just come down to what your hosting provider is offering. I must admit that I am biased towards PHP/MySql :)

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

3

Re: PostGIS (PostgreSQL) or MySQL?

PostGIS.

We've been using it for five years now and it's proved very robust. MySQL still does not support basic geospatial functions, like Buffer() which is a critical function. I know how popular MySQL is, and it's included by default with lots of ISPs. If you are into PHP (I'm not a fan, but needs must) then Postgres support in PHP is there.

Postgres has installable procedural languages e.g. java, ruby, perl and python, as well as the default  built in PL/pgSQ which I use for stored procedures and functions. I also love the ability to partition schemas - these act like folders. Keeps the database structure neat and organised. 

As for hosting, for small bits of data going to the web site that comes from an internal server. We rent a UNIX VPS at a London data center and put larger datasets there on a replication basis. Postgres and PostGIS have a UNIX heritage, but the windows installer is really very very good.

There is slow but increasing support for directly looking at PostGIS layers from GIS - Quantum GIS is one, GRASS another and there is ERSI support on the cards. The supplied shapefile dumpers (shp2pgsql and pgsql2shp) make it easy to get spatial data in and out.

Sorry...completely biased, non objective comment!

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

4

Re: PostGIS (PostgreSQL) or MySQL?

Thanks for the pointers guys, much appreciated. If I may be so bold as to ask, what are you actually doing with your systems; i.e., what can the end user(s) do with them?

This looks interesting:

http://abegillespie.blogspot.com/2008/01/ziggis-20-official-announcement_07.html

It's looking like PostgreSQL is nudging ahead at the moment, simply because it seems a bit more advanced, plus it is being supported by ArcGIS in version 9.3.

I'm actually also quite tempted by the new ArcGIS Server. That might ultimately require learning .NET or Java, but I'm not adverse to that as such.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

5

Re: PostGIS (PostgreSQL) or MySQL?

Good news about ArcGIS. We are migrating to ESRI kit, and I'd hoped that the support would be there by the time we do.

PostGIS runs all of our internal spatial enquries, generates spatial datasets for internal GIS and data products going outside. We have a couple of applications which are semi-automated where data from outside organisations gets uploaded into the database for searching and then reporting.

It hold subsets of biological data in a form for fast web queries and interface to web mapping. (Mapyrus). This is not quite there yet as version three of our web site is under testing. Also, our intranet and extranet applications use Postgres.

The link to Recorder is one table which holds fields from the SAMPLE table. This is updated automatically on a daily bases, or manually as needed. External scripts join spatial results to Recorder's data and put that back into Postgres as records and taxa. Our system uses 'tickets' to reference jobs and queries.

We also hold constraints layers such as VC boundaries, wildlife sites, roads networks, SSSI, SAC etc. These mirror the layers on the desktop GIS, so any programmed GIS work using PostGIs uses the same layers.

So, in short, we use it as a programmable GIS and as a "normal" DB to hold managment information.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

6 (edited by davec 01-05-2008 10:06:08)

Re: PostGIS (PostgreSQL) or MySQL?

I forgot to mention how users interact with the system. The answer is they don't! (well not via a GUI). . The system is driven by external scripts and these scripts will be unified by web applications. That's the bit I'm still working on. Having bricked myself into a corner a few times, I've settled on using Adobe Flex to build the user interfaces as this can be run locally (using AIR) or out on the wild web using Flash.

Once the interfaces are build (Adobe Flexbuilder seems really good. Having tried this we have ordered it) these will act as input/output to the scripts.

Cheers.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

7 (edited by Nick 01-05-2008 16:17:55)

Re: PostGIS (PostgreSQL) or MySQL?

Hmm... I agree, MySql does not have Buffer functionality but as far as I know that is something that is being developed in conjunction with other GIS functions. So I guess it is a little behind in that respect. The one thing that I would say is that MySQL is more common as the database provided by hosting companies - not in itself a reason to use it but by inference there are probably more users out there developing solutions that might be of help to others.

So for guys like me who build everything from the bottom up (particularly if you are reluctant to splash out on Adobe Flex, ArcGIS Server etc.), then perhaps PHP/MySql is not a bad starting point for cutting your teeth on web gis development.

So I guess it is horses for courses ;)

EDIT - forgot to mention, users access the system through PHP pages e.g. http://www.nesbrec.org/thesaurus2.php (searching on NBN's dictionary) giving a result e.g. http://www.nesbrec.org/species_map.php? … ra%20lutra based on live data updated nightly using Navicat. If you click on a grid square you get a more detailed map. 

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

8

Re: PostGIS (PostgreSQL) or MySQL?

Dave, any reason you chose Adobe technologies (AIR/Flex) over plain old HTML and Javascript (Ajax)?

Nick, as a long time user of PHP and MySQL, it's tempting to simply use those, but I've started using other technologies lately (including Ruby and Rails), plus we already use ESRI kit, so the benefits of using PHP and MySQL aren't quite so apparent here. I still use them for our basic website, though.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

9

Re: PostGIS (PostgreSQL) or MySQL?

Nick wrote:

particularly if you are reluctant to splash out on Adobe Flex

I didn't realise this either, but Flex is free and open source. That just made it a whole lot more interesting to me. A whole lot more interesting indeed. It's just Flexbuilder, the IDE, they charge for. I was considering what I should use for desktop development (I was considering .NET, mainly), but this seems like it may be a better alternative given my web background and preference. I'd always been put off because I loath Flash (the application, rather than the technology itself). I might have to revisit it I think.

But, argh. Just too much stuff to play with and too little time!

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

10

Re: PostGIS (PostgreSQL) or MySQL?

Just been looking at this screencast and Flex does look very compelling indeed. I'm definitely going to look into it further for desktop apps. Not sure I'd use it for a full website, but for the rich app component, charting, visualisation and suchlike, it looks a winner.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

11

Re: PostGIS (PostgreSQL) or MySQL?

Thanks Dan, you're not too late at all. I don't rush into these things (I tend to let others rush in, then learn from their experience). Seems like PostgreSQL is definitely the way to go.

Thanks again.

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

12

Re: PostGIS (PostgreSQL) or MySQL?

charlesr wrote:

Dave, any reason you chose Adobe technologies (AIR/Flex) over plain old HTML and Javascript (Ajax)?

I find HTML/JS/AJAX just such a clunky way of doing things. I know people have done wonderful things with these technologies, but I find it hard work. Flex offers desktop type controls and interaction and I can build my interface visually and script events in one IDE then just run it in the flash player or on the AIR runtime. Flex also has great data controls which are vital for our work.

I see this as a set of interfaces which sit embedded within a web page within a CMS - we are using Drupal as it's on tap at our ISP. As noted, we got Flexbuilder free via the Adobe educational program.

Sorry for the late reply, I've been on leave.

Dave Cope,
Biodiversity Technology Officer,
Biodiversity Information Service for Powys and Brecon Beacons National Park.

13 (edited by AlanHale 21-08-2008 09:40:31)

Re: PostGIS (PostgreSQL) or MySQL?

My reply's later than any of them! (Have been otherwise engaged lately discovering what I can do with Google Earth).

Did you reach any further conclusions or made any further progress with this Charles?

The problem is there are a plethora of options, each requiring a lot of input and time to learn properly. Like others I already had experience in PHP and MYSQL and for web-mapping eventually went down the Mapserver, PHP-Mapscript, MySQL route. After a LOT of hair-tearing trying to manually configure Mapserver I discovered the FGS package which basically sets everything up for you in separate server environment. I also ended up eventually using Javascript msCross http://mapserver.gis.umn.edu/docs/howto … ng-furtherfor the interface. This has limitations (like no way I can see to query the map) but is simple, fast and effective in many ways.

One of my rather primitive efforts (I only do this part-time) is here:

http://www.bsbimaps.org.uk/tetrads/main.php

I'd be interested in seeing examples of what other people are doing and hearing what technologies they are using.

Cheers

Alan

PS - the FGS community seems small but very helpful

14

Re: PostGIS (PostgreSQL) or MySQL?

Hi Alan,

Not really come to any hard conclusions yet. This area of IT is moving so fast, it's best to stay as agile and agnostic as possible.

As far as mapping goes, I (we) have been looking at OpenLayers lately. No live demos to show off yet, but soon, hopefully. ;)

Charles

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

15

Re: PostGIS (PostgreSQL) or MySQL?

Hello to all!

I've been developing a webgis based entirely on recorder database.

There’s already a public version with some minor bugs and is currently living at ---------> http://internal.bio3.pt/recorderlive

This version is a bit slow when running some queries, because all this data is being processed on the fly, with will end up delaying the application a bit.

By this approach which is powered by the recorder database without any middle DBMS or any tools besides of course the GIS platform behind it intended to understand some problems related to the delivery of the recorder data base online using a webgis.

The next step is working the integration and strategy of delivering this data over the internet,  in a faster and better way.

Regards.

[img]http://img90.imageshack.us/img90/1174/assinaturazx9.png[/img]

16

Re: PostGIS (PostgreSQL) or MySQL?

Hi Gustavo

The initial interface looks good but (as you say) the system is a bit slow. I therefore wonder whether it will be even slower once you add some background mapping?

I am also not clear what the advantage is for your proposal - can you not query the data from the database and use existing systems (e.g. OpenLayers - Charles I too have been testing this out) for displaying? So unless the system is really fast, most people may not get the best out of your system.

Incidentally, will your solution run on PHP?

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council

17

Re: PostGIS (PostgreSQL) or MySQL?

Hello to Nick.

Nick wrote:

…the system is a bit slow. I therefore wonder whether it will be even slower once you add some background mapping?
Nick

About this question, no it won’t be slower unless you add really huge cartography files to background, however I don’t believe that can occur (getting slower) because I already tried that with other projects.

Also about speed (or getting a bit slow) it’s happening because when the client connects to the server he is actually connecting and processing all the information on the fly which will end up creating some views that can be later queried by the client and are also displayed in the map.

So, to make this approach faster it’s easy, by using the DBMS crate views or event schedule a script for creating and deleting tables during maintenance time will improve a LOT the speed.

This approach was intended to test&identify the potential issues regarding recorder development and try to understand how heavy the database can be, which will reflect the amount of processing requisites for a feasible web application.

The next step is to perform a few server programming to improve the speed that will improve the above.

About queries available you can find a few of them mainly in 2 locations.

The “Query Manager” allows you to select a few of them, however more of them could be added, it just depends of what you want to allow the user to query, in these case since I’ve a lot of work haven’t the time, so I just recommend to try it.

There’s also spatial queries already available (more info with images here http://internal.bio3.pt/recorderlive/concept.html) . By using the buttons you can perform  2 types of queries; on one hand you can create a point, build a buffer and select the action for that buffer (taxon or biotope); or on another hand you can build a polygon and get the associated data from it.

Nick wrote:

…. Can you not query the data from the database and use existing systems (e.g. OpenLayers… Nick

Basically, Openlayers is one of my favorite opensource GIS visualizer , but I´ve never tried OpenLayers for Recorder GIS but I believe that’s possible to get something interesting from it.

Regarding this platform I’m more like a user not like a developer for the moment.

Nick wrote:

Incidentally, will your solution run on PHP?
Nick

It’s possible to run on PHP however this example is pure ASP and it's bsed on a commercial GIS application not opensource.

Regards.

[img]http://img90.imageshack.us/img90/1174/assinaturazx9.png[/img]

18

Re: PostGIS (PostgreSQL) or MySQL?

Hi Gustavo

Many thanks for the info - keep us posted of your development

Cheers

Nick

(ePlanning Project Manager) Aberdeenshire Council