At the end of part 1 I was left with openMAINT not recognising postGIS.
At the end of part 2 I have the map in openMAINT, but no building icon on the map, or plan of building either, but there is a progression.
After configuring openMAINT as per the CMDBuild documentation, I decided to go exploring in other areas and investigate PostGIS and GeoServer. So Mr YouTube was my friend.
This first video, in Arabic, but just watch the process shows a couple of things:
- The fact that PostGIS needs to be activated as an EXTENSION in PostgreSQL database
- The link between geoserver & postGIS
- How to start up and link the 2 together.
Item 1/ Above, I went looking on the postGIS website install section and saw that there was an activation process, as it says, do not do in the posgres database.
I used the first line: — Enable PostGIS (includes raster) CREATE EXTENSION postgis;
Testing it in SQL in pgADMIN3.
I have chosen a deatabase “omback” to run the SQL query on. And got an ERROR.
It is looking for the postgis.control file in a directory share/postgresql/extension
Whereas in my setup this file is sitting in a directory share/extension
So, I copied a lot of the files from the share/extension directory to share/postgresql/extension and got a further error inv that it couldn’t find a $lib directory.
I searched online about this issue and the common theme was that I loaded up the programme wrong.
My original load was PostGreSQL 9.3 then loaded after postGIS.
From the video tutorial above I saw that the process was a single download of postgreSQL with Stackbuilder which installed postGIS for postgreSQL, as it downloads the version that is compatible.
So I went back and downloaded the combined installer here.
Follow link, chose version, choose operating system and then download.
BACKUPS & More BACKUPS
So I backed up all my databases, remember to backup “alfresco’ too!!!
Also, I backed up INDIVIDUALLY
- public schema
- gis schema
- shark schema
Make sure you back them all up as you may be using these later.
Then I deleted my postgreSQL installation and reinstalled it. I deleted the original directory.
You may have to reboot a couple of times and check your postgres SERVER is off in your windows task manager), then I created the same directory and installed postgreSQL back into it (then the tomcat maps to it as per the original if all the directories/sub directories are the same).
If you don’t put it in same directory I think you may need to set up openMAINT from scratch (not absolutely sure on that though)
I then created a blank database and restored my Database for GIS/Public/Shark (the openmaint setup) then tried to install the extension postGIS but again had problems.
So I installed the postGIS extension first and then loaded up the other schemas (public/gis/shark) and then ran openMINT, and again it did NOT recognise the postGIS installation!!!! AAAAAgghh.
In fact, you have to run the SQL on all the extensions above, then it works:
-- Enable PostGIS (includes raster)
CREATE EXTENSION postgis;
-- Enable Topology
CREATE EXTENSION postgis_topology;
-- Enable PostGIS Advanced 3D
-- and other geoprocessing algorithms
-- sfcgal not available with all distributions
CREATE EXTENSION postgis_sfcgal;
-- fuzzy matching needed for Tiger
CREATE EXTENSION fuzzystrmatch;
-- rule based standardizer
CREATE EXTENSION address_standardizer;
-- example rule data set
CREATE EXTENSION address_standardizer_data_us;
-- Enable US Tiger Geocoder
CREATE EXTENSION postgis_tiger_geocoder;
Now, as I was in multiple installation mode, I only setup my restore database partially. I just wanted to test to see if the GIS section could be activated, and it was.
The OpenMAINT initialisation
(Note in image below I have latitude and longitude the wrong way around. )
This is for initialising the GIS
Then go to item 6 above
The icons I used are from Freepik on the flaticon.com website. You are allowed to use for free but have to acknowledge the author. Thankyou Freepik.
For item 5 I am using a shape file that I generated in QGIS. So see below for the explanation of how that was created/saved.
Also, in the video at the start, it shows how he uses a plugin in postgres to import a SHP file into postGIS database (into a new table). I am still figuring all this out.
openMAINT Administration Module
So, as far as setting up goes, I have done a preliminary setup , and on the Administration panel I have, the map.
Unfortunately I cannot stick pins into it. So map with no content.
The main tab has a map icon in the top right corner now.
clicking on the map icon shows you the map and a data box to the right with 3 tabs.
Where the map icon was is a grid with list, click on that and it takes you back to the previous image.
The thematism next to the the list icon. See this video from CMDBuild where they talk about it ( go to about 15 minutes into the video)
The 3 tabs are shown in these 3 images, I’m still trying to figure out what they mean.
So, at this point, I have a map in openMAINT without any map icons in the map relating to my buildings. Note, as I deleted my original database setup currently I have basic openMAINT data but have not got shark workorders or Alfresco running in this instance.
This is fine at present, as I am just trying to understand the GIS part, but it all needs to come together so all components work.
Geoserver, part 2. Connecting to postGIS schema
Now I want to go and look at some of the Geoserver items.
As I was trying to figure the map in openMAINT out I came across information about Geoserver. I am still not sure if its needed for pins in map, or if its only used for overlaying Vector information (rooms/plans etc) onto the map, but as this is all part of the GIS stuff I will put down what I have found.
Within the whole GIS area there is a lovely programme called QGIS, which is a pretty impressive programme that is free. I had played with it before for another exercise.
Download it here. A basic beginners guide to QGIS here. I focused on layers, saving them and points (for putting flags in openMAINT) and saving SHP files.
My objective with this programme was to create some coordinates and save them to a SHP file that I could either download into Geoserver or postGIS.
This was successful. I created the data, it may be a bit limited and I may need to develop it further, but the zipped file can be downloaded here.
Although I got the shape file into Geoserver & postGIS I still was not able to have a point on the map with a flag/icon on it. This is for a future blog. A bit more research required.
Beware. Because this is a pretty sophisticated programme it needs a bit of power from the computer, so if you have a light computer it will be slow.
At the end of this we have done the following actions:
- Reinstalled the postgreSQL database and postGIS add-in
- Made a postGIS set of extensions (7) for a total of 8 extensions in postgreSQL
- Used QGIS to create a SHP dataset of points that can be saved as a file
- Have Map working in OpenMAINT after a basic configuration
- Have Geoserver connected to postGIS schema? or are we linked to the database?
What we haven’t got working, at this point because of the test environment focusing on the GIS
- Shark & Alfresco, as we rebuilt the database
- Icons on the map in openMAINT related to the building that we want to associate it with.
So in reality, we haven’t got too far at all. Hopefully the next installment will answer a few more questions.
I would like to point bout that there is nothing in the openMAINT or CMDBuild about the extension requirements of postGIS in the postgreSQL database. They really are not a helpful bunch of people. I honestly do not understand why they are calling this open source if they do not give you the fundamentals to set it up. I believe that the university set this up, not tecnoteca, so they are just maintaining it to their own advantage.
What is getting my head scratching is why is there NOT a simple Longitude/Latitude cell to put in coordinates of the building? Floor?Room?Component?
Do we need to create these attributes? they should logically just be in there.
Another test I could do is to take the original database DEMO and install it to see if there are Longitude/Latitude attributes to fill in.