I've been having a bit of trouble understanding how a dataset is built/managed for GIS applications.
Over the past few weeks (part time) I've been trying to understand the basics of GIS, and how it works with the likes of GeoServer, QGIS, etc... in terms of loading in layers, and displaying them, but not the style editor yet, although that looks straight forward enough. However I'm at a loss as to how the variable data should be stored.
For example, while experimenting added a base map layer to QGIS and added a new layer with a few points on it. I was able to add some parameters to those points, such as an Id and a state boolean.
In QGIS I could add rules then, like if state is true set the point red, otherwise set it green and they would work as expected.
However, in my situation, where I have a database full of node Id's and another database full of GPS co-ordinates, I'm not sure how to format that information in order for GeoServer to be able to parse it properly from my PostgreSQL database.

At first I thought that the points would be stored in geometries and the related dynamic data would be stored in another table linked with an ID or in the same table in an new column.

In order to try deduce this further I exported my points from QGIS in GeoJSON format and got this:

"type": "FeatureCollection",
"name": "point_test",
"crs": { "type": "name", "properties": { "name": "urn:ogc:def:crs:OGC:1.3:CRS84" } },
"features": [
{ "type": "Feature", "properties": { "id": 1, "upgrading": 1, "nodeId": 123456 }, "geometry": { "type": "Point", "coordinates": [ 13.09283649916585, 55.808940217262837 ] } },
{ "type": "Feature", "properties": { "id": 2, "upgrading": 0, "nodeId": 654321 }, "geometry": { "type": "Point", "coordinates": [ 13.030371871652321, 55.959245727217265 ] } },
{ "type": "Feature", "properties": { "id": 3, "upgrading": 0, "nodeId": 987654 }, "geometry": { "type": "Point", "coordinates": [ 13.166037234533263, 55.924109374240864 ] } },
{ "type": "Feature", "properties": { "id": 4, "upgrading": 1, "nodeId": 456789 }, "geometry": { "type": "Point", "coordinates": [ 13.167989254143061, 55.914349276191871 ] } }

Which has the dynamic properties embedded in the JSON itself. However if I export the layer as an Esri shapefile then I get multiple files, and the properties are separated out in a separate file, in what looks a little more like a traditional database dump, along with some other files. That export looks like this on the drive:

Esri Shape Files

With a few of them not being human readable.
I can import these individual layers into GeoServer no problem, but with the large amounts of data I'll be using it's not practical to add each point in QGIS and export them for use in GeoServer. I'm imagining I'll need to build a service that queries the information I need from multiple databases then builds point objects or geometries from those various sources then dumps them into a postgresql database for GeoServer to use.

So how does something like that normally look in a database? Will those dynamic properties be embedded in a json like structure and queried from there? Or will the dynamic data be stored elsewhere with some sort of ID tying it to the geometry/point?

With the shapefiles, looking at the file ersi_point_test.dbf I can see the dynamic properties stored in there, (and when I click on a point on the map served by GeoServer it displays the correct information) but I can't see a relationship between the geometries/points and the dynamic data; it's obviously located in one of the non-human readable files.

  • 1
    You are mixing separate questions into one. What exactly is a database to you? Are we talking about how to ingest your geodata into PostGIS via QGIS? Or is it strictly about QGIS -> GeoServer in any way? – bugmenot123 Nov 9 '18 at 14:54
  • 1
    Internally Postgis stores a geometry as a binary object (wkb). GeoJSON is simply a human-readable and very convenient interchange format. You can store data directly in JSON format, but you will lose much of the functionality of storing it directly as a geometry format. You can always convert back using ST_ASGeoJSON function. As already stated, shp files are a difference beast, and store the geometry, attributes, spatial index, etc in separate files. – John Powell Nov 9 '18 at 17:18
  • 1
    @ bugmenot123 Yes, apologies, for that. There's a lot to take in with GIS, I'm a bit lost. I've been experimenting with both QGIS to understand how maps work. What I want to understand, is when properties are associated with a geometry or point, how should they be stored in the database, and how are they then referenced by geoserver? is it using filters/styles? something else? does geoserver automatically pick up the properties associated with a geometry if they are stored on the same row in the database? – bot_bot Nov 12 '18 at 8:57

I am not sure what the actual question is, so here I answer what the title says:

What you got when you exported a Shapefile is actually no like a database at all. It is a mess of loosely connected, easily deleted files. There is information about the encoding of text in the attribute file stored in another file. The projection of the geometries is stored in yet another file. The geometries of the features are separate from their attributes. It is a ancient and quite horrible mess of a format.

In your GeoJSON snippet above, you have a simple list of features. Each of them has a list of attributes and its geometry. That's so nice!

In a database like PostGIS you usually also store the geometries and attributes of the features together in the same table. Strictly speaking, if you did not do so, you would need to "join" the attributes in a separate processing step, which is not ideal.

| improve this answer | |
  • Thankyou! So if I have table row with the geometry and an attribute, then I can access that in geoserver using filters/styles? – bot_bot Nov 12 '18 at 7:50
  • 1
    Yes! That's the way it's meant to be done :) – bugmenot123 Nov 12 '18 at 10:11
  • OK this is great! I think it's finally clicked! many thanks! :-D – bot_bot Nov 12 '18 at 11:49

Not the answer you're looking for? Browse other questions tagged or ask your own question.