pg_featureserv - Publication of vector data with OGC API features
This is a modal window.
The media could not be loaded, either because the server or network failed or because the format is not supported.
Formal Metadata
| Title |
| |
| Title of Series | ||
| Number of Parts | 156 | |
| Author | ||
| Contributors | ||
| License | CC Attribution 3.0 Unported: You are free to use, adapt and copy, distribute and transmit the work or content in adapted or unchanged form for any legal purpose as long as the work is attributed to the author in the manner specified by the author or licensor. | |
| Identifiers | 10.5446/68512 (DOI) | |
| Publisher | ||
| Release Date | ||
| Language |
Content Metadata
| Subject Area | ||
| Genre | ||
| Abstract |
| |
| Keywords |
FOSS4G Europe 2024 Tartu92 / 156
6
33
35
53
55
59
61
67
70
87
97
99
102
103
104
105
107
111
121
122
123
124
125
126
127
128
134
144
150
151
155
00:00
Process (computing)Streaming mediaSoftwareComputer networkOperator (mathematics)Enterprise architectureService (economics)PlastikkarteTransformation (genetics)Computing platformAutomatonInterface (computing)Configuration spaceFunction (mathematics)DatabaseVector spaceComputer fileServer (computing)File formatRaster graphicsComplex (psychology)Access SQLQuery languagePrice indexTable (information)ConsistencyDatabase transactionExtension (kinesiology)System programmingCoordinate systemSinguläres IntegralBinary fileComputer-generated imageryAddress spaceGeometryMetadataPolygonFirst-person shooterEncryptionEmailWebsiteUniform resource locatorVector spaceRaw image formatDatabaseQuery languageType theoryFunctional (mathematics)Web-DesignerPairwise comparisonInformationWeb browserCASE <Informatik>File systemDefault (computer science)Data typeDifferent (Kate Ryan album)Open sourceFunction (mathematics)Contrast (vision)Normal (geometry)TunisComputer fileConfiguration spaceOperator (mathematics)Physical systemCuboidMultiplication signWindowOrder (biology)Parameter (computer programming)EmailIntegrated development environmentEncryptionComputer programmingParallel portSubject indexingMetadataSet (mathematics)Variable (mathematics)Theory of relativityGeometryUser interfaceSoftware developerWeb 2.0Service (economics)Validity (statistics)Diallyl disulfideSpherical capTransformation (genetics)Process (computing)Source codeSlide ruleMappingInstallation artLink (knot theory)Standard deviationFormal languageExtension (kinesiology)ConsistencyComplex (psychology)Binary codeMedical imagingMultiplicationShape (magazine)Uniform resource locatorAxiom of choiceOverhead (computing)File formatGoodness of fitSoftwareServer (computing)Connected spaceFunctional programmingData storage deviceProjective planeFiber (mathematics)InternetworkingTelecommunicationRepository (publishing)WritingDirection (geometry)Reading (process)PlastikkarteShared memoryTable (information)Operating systemLecture/ConferenceComputer animation
09:33
DatabaseFirst-person shooterEncryptionEmailWebsiteFunction (mathematics)MetadataUniform resource locatorTable (information)Configuration spaceComputer configurationSample (statistics)Asynchronous Transfer ModeIntegrated development environmentVariable (mathematics)Open setCategory of beingQuery languageQuery languageExecution unitProjective planeFunctional programmingWeb browserMathematical analysisParameter (computer programming)Data typeLanding pageDatabaseTesselationWeb 2.0Latent heatMedical imagingForm (programming)Structural loadFunctional (mathematics)Wrapper (data mining)Connected spaceDirection (geometry)User interfaceEvent horizonRepetitionQuery languageLink (knot theory)Physical systemComputer programmingResultantSystem callPower (physics)Coordinate systemIntegrated development environmentVariable (mathematics)Table (information)Configuration spaceMultiplication signRange (statistics)Formal languageType theorySet (mathematics)SoftwareService (economics)Computer fileStandard deviationCategory of beingDescriptive statisticsDatabase transactionExterior algebraTunisElectronic visual displayBitAttribute grammarGraph coloringKey (cryptography)Template (C++)Inheritance (object-oriented programming)Server (computing)Level (video gaming)Vector spaceNetwork socketNumeral (linguistics)Representational state transferCuboidDot productComputer animation
18:54
Link (knot theory)CodeRepository (publishing)EncryptionFirst-person shooterEmailMetadataWebsiteDatabaseTable (information)Function (mathematics)Uniform resource locatorRight angleFunctional programmingConfiguration spaceDatabaseStandard deviationComputer programmingInterface (computing)Level (video gaming)Computer fileSlide ruleTemplate (C++)View (database)Functional (mathematics)2 (number)Category of beingProcess (computing)Flow separationComputer animationLecture/ConferenceMeeting/Interview
21:49
MetadataUniform resource locatorTable (information)DatabaseFirst-person shooterEncryptionFunction (mathematics)EmailWebsiteSoftware testingConformal mapWeb 2.0Standard deviationFlow separationView (database)Row (database)Client (computing)Table (information)Server (computing)Configuration spaceComputer animationLecture/ConferenceMeeting/Interview
23:02
Computer-assisted translationGoodness of fitComputer animation
Transcript: English(auto-generated)
00:00
Hi everyone, thanks for introduction. So I will present you the tool called PG feature serve where you can publish raw vector data via OGC API features from a PostGIS database. So just quickly about me, my name is Jakob Mieksch, I'm a software developer at the company CityCom.
00:24
We do all kinds of telecommunication solutions and my department is about broadband fiber internet and smart infrastructure. And so we're building an internal GIS system and we're evaluating many projects, also PG feature serve.
00:46
And finally we didn't use it but I learned a lot about it and also used it in a private project. So that's why I would like to share my learning with you and maybe it will be helpful for you if you're considering it in a new project. So first the overview. I will give you an overview about PG feature serve, then also
01:08
why it's only reading from a database and why it's only publishing via OGC API features. How to install and configure it, then I want to show you the web interface and how you can filter and use Postgres functions and then also some examples where it's used.
01:26
So first of all, what is PG feature serve? It's a very lightweight geo data server. So it can only read from one data source, it's PostGIS and published only one standard, it's OGC API features which is the successor of WFS.
01:46
It's written in Go language and this is quite nice because it's relatively fast but also quite approachable language. And for example if you compare it to C++ and also it makes it very simple to install.
02:03
It was developed by CrunchyData, by Martin Davis and Paul Ramsey and you can find the repository on the link I show on the slide. And just to show you what PG feature serve is and what it's not, I want to compare it to geo server which is to my knowledge at least the most mapping server with the most sources and styles.
02:29
So on the left side you see so many different sources you can have and so many different formats you can publish with geo server but with feature serve you can only publish from PostGIS and only via one standard.
02:47
And this can of course be called, you can think it has very low features but sometimes when you only want to publish especially vector data then it's very nice and very easy to set up.
03:03
And then it might be a really good choice because there's no overhead, you don't have to install stuff which you don't need actually. So in some use cases it's quite useful. So why is it actually only reading from a database and not from files like shapefile or geo package?
03:23
So first of all with a database so in this case PostgreSQL you can use SQL for all kind of complex queries. So you can have multiple datasets in the various relations and make some joins and you can really combine a lot of datasets with using SQL.
03:42
You have a very high performance for querying because you can create indexes so spatial indexes or non spatial indexes. Of course many people can parallel request data, you can directly write data and read data at the same time. You can have different permissions for different users or programs which access the database.
04:05
The data consistency is all guaranteed and the other popular examples for databases in the open source world is PostgreSQL, MySQL or SQLite. And PG feature service of course using PostgreSQL which should be known for from I guess by most of you.
04:23
So I would say in the open source world it's the de facto standard especially because of the PostGIS extension which creates a data type especially for geo data and also has some processing capabilities, coordinate transform, transformation and validation possibilities.
04:42
And it's also can be used by a lot of different software like QGIS desktop, QGIS server and GDAL or GR. It has a wide support so it could for example use QGIS to edit your data inside PostGIS and PG feature serve to publish your data.
05:06
Then the next thing is it's PG feature service only publishing one standard. It's the OGC API features standard which is the successor of the web feature service and it shares a raw vector data.
05:24
Actually the standard has a growing support for example QGIS can read it and apparently also write it but I have not tried it yet. And one really cool thing is that in contrast to WFS you can actually view the standard in a normal web browser using the HTML output.
05:47
So earlier with WFS if you want to get some information you have to manually read through some XML and now you have it nicely displayed as an HTML and JSON is the default exchange format.
06:01
So we can of course use a lot of different things but JSON or GeoJSON in this case is the default. And I will quickly show you some comparison how these request works. So you have a base URL and if you want to get information about what layers or collections you have you
06:23
just type collections and you get a JSON back and with WFS you just get capabilities and you get an XML back. So OGC API features is REST based so you have this order here, you have the collections, then you write your collection name, then you get the items and with WFS you have this query parameters.
06:47
And I think many people find this more intuitive than this one because you can still type it in the web browser directly and see the HTML directly.
07:01
Here you can also directly see the feature ID when you want to see one specific item. And you can also do some querying like you can query for the city of Tartu with some filters. So the next thing is how to install it. Like I said before it's written in Go so it's actually one binary you can download for Windows, Linux
07:30
and Mac and you just place it somewhere in your file system and start with command line and it runs out of the box so no complicated installation. There's also a docker image but actually the binary is so small and so
07:44
easy to install that the docker image is almost like too much trouble to install. Binary most of the time works quite well and it works on all major operating systems. Then for configurations there's a few ways but actually when you just start it
08:05
there's no need to configure it a lot because it directly takes every geospatial layer. So if there's a layer with a geometry column it will instantly be shared as a layer so this is quite nice.
08:24
But you can if you want you can also do some tuning. There's a configuration file and there's some environment variables and also some command line arguments but most of the time you don't need them. So there's also a schema which stores a lot of functions which you can also execute later
08:45
on with PgFeatureSurf and one interesting thing is that also metadata is stored inside the database directly. So you can use the comment functionality in Postgres and write some comments about your table column and so on and it will be visible in the OGC API feature.
09:06
So they basically try to move most functionality that's possible inside PostgresQL and this can be quite elegant. Then you have a configuration file where you can define the database connection. It also has some
09:26
HTTPS encryption and adding a course header because very often in a web development environment you need this. And you can do some fine tuning if you want to exclude some schemas which should not be published or include some tables and so on.
09:45
So there's a lot of fine tuning you can do. And finally you can also overwrite the configuration file with some environment variables which can be useful for if you run PgFeatureSurf in
10:02
the Draco Compose environment and also some command line options but they are very few so no much trouble to set it up. Then how does it look like? So when you start it you have a landing page where you have the links to the collections and to which layers are published.
10:25
So here you can when you go to conformance you can see which standards are supported. He can go here to collections and you see like the layers which are published and here when you see it it's slash collections dot HTML.
10:47
So then you can see it as a human in a web browser but when you replace the HTML with JSON then it will be instantly a JSON and it's super convenient because then it's also nice for debugging and you have you're much closer to the data in my opinion.
11:02
Then yeah so you go to collections then again put a layer name here and then you get some nice description about coordinate reference system about the properties which data types they have and so on. There's also a built-in map functionality so you can directly see it and have some quick look where data is and
11:25
also some little filtering possibility and here you can just replace the HTML with JSON and then you get the raw data. Here you can also look at the specific feature only then you get the attributes what it has.
11:44
And the HTML theme I showed you is adaptable so it can it's written in Go HTML so it's a templating language and let's say if your company wants to have a specific logo or you want to have your special colors you can create your own theme and you can just mount it to Pg feature serve.
12:04
And so you don't need to change the original program you can just add your custom theme additionally and it's quite simple to to adjust it as into your wishes.
12:20
Then the next thing is I want to show you some filtering what you can do so here you have a collection you have countries and then you can do some very simple filtering. It's just when an attribute like a key you can write the name of an attribute name and then also of the value and then you directly get the result which this is quite convenient.
12:46
So because when you in WFS is a bit more complicated and but you can also do some more complex queries with the CQL language here. It looks like this but the problem the only problem is that you have to encode it because otherwise the browser doesn't understand it.
13:03
But you can already do some filtering and all the filtering is actually done inside the PostgreSQL database so the Pg feature serve the scope program is only a thin layer between the HTTP requests and PostgreSQL. Then you can also do the searching directly and web browser you just type a name here of the feature and then
13:27
it will be hit you will get the hits so there's apparently two cities called Lima in the data set I used. And the CQL language also supports some more filters so you have temporal filters you can if your data
13:45
has date type you can filter by before after of a time range of course geographic filters if you're only interested in a specific bounding box or wanted intersection of a feature and numerical filters or text filters.
14:00
And if this is not enough for you you can actually define database functions and then you have the full power of PostgreSQL and then this database function is exposed outside to an HTTP call and then a user can just go to this
14:21
end point and then inside the database function is triggered and the result is returned so I give you a very simple example. Which I took from the official documentation so here's a Postgres function which just looks for a
14:41
feature which starts with a specific name prefix and then you can also write a comment here. And then it will be published here via like the base URL slash functions and Postgres for the web. It's a dot place names it's the function name and dot HTML you get an overview of the function what it
15:07
takes as a parameter and what kind of return you get and then you can just use it on a web interface. And for example here I'm looking for all cities starting with HEM for example Hamburg in Germany but there's also some other places
15:24
and then you can directly do some kind of spatial analysis inside the browser which is using some Postgres functionality in the background. So just to round it up just some similar projects that there exist so there's a kind of
15:42
sister project to Pg feature serve it's called Pg tile serve and it also reads from a Postgres database but publishes vector tiles and there's an interesting podcast episodes on the link here from appscaping podcast. Another sister project is called Pg event serve it does not have a direct geospatial connection but it can so it basically uses
16:08
so-called Postgres notifications and redirects it to a web socket which you can direct which you can use via an HTTP request. There's also a podcast episode about it which is quite nice and then another program is called Postgres
16:24
which creates a REST API from your Postgres database or Postgres database which can be very useful as well. It's not no OTC standard or something it's just like a REST API but it can be useful as a thin wrapper between your web browser and database.
16:43
Then as an example so there's a program called OSM2PGSQL where you can load OSM data into Postgres and there's an example on the website how to publish this OSM data I can recommend having a look there because it shows some basic features.
17:05
And then there was one interesting example where someone created PostgreSQL functions which are embedded inside a Google Sheets form. And so this PostgreSQL function will create finally SVG files or JSON files so basically you can create
17:30
like images or SVG graphics on demand directly inside the PostgreSQL database and display it on the web anywhere.
17:40
So this shows that you can use your creativity to write really sophisticated PostgreSQL functions and expose this functionality as an API via a SVG feature reserve. Alright then there's also some more alternatives I want to show there's I have the feeling in this world
18:06
of like really lightweight geospatial service software publishing services there's a lot of more which you can check out. And finally just a final conclusion there's Pro it's very lightweight easy to install it has some most of the functionalities are included and has a
18:28
simple web interface but contra argument is it's only for vector data only the very new OGC API features and some systems still rely on WFS. So sometimes it's not working and there's no transaction yet so no possibility to edit the data
18:47
in PostgreSQL but there are some pull requests working on that so it might come in the future. Then finally some links for you if you want to explore more and yeah then I'm finished and thanks for your attention.
19:11
Well thank you for this talk and let's hear some questions. Yes we have one question over there.
19:29
For the interesting talk I was wondering whether you showed the functionality to use PostgreSQL functions like custom PostgreSQL functions that might be related to OGC API processes somehow?
19:48
Or is it related or it looked like like a custom endpoint or like custom resource you created right? Yeah very good question I thought about this as well so this is custom so it's not OGC API processes but
20:05
this could actually be done that this process or like this function could be also exposed as a OGC API processes interface. So I think it should work so but it's not currently it's just like
20:22
a custom endpoint which doesn't follow any other standard to my knowledge at least. Okay thank you very much and I actually have a second question if that's fine. So what is exactly included in the binary so you said it's just a small binary I have to download
20:41
so it's just the Go program or is the database included or do I have to install the database separately? Yeah you have to install the database separately. Okay so it's just the Go stuff. Yeah exactly. Thank you.
21:01
Any more questions? Do you have one? I'm ready. Yeah I want it. I have a mic so. Short question. What's the way how to use custom background map in the map you like to customize the template?
21:25
Yeah you can definitely so there's let's see if I have a slide. Because I haven't found it in a configuration file so I guess it's like customized. Yeah but there for sure. Yeah I'm in the configuration file there's like a property where you can define custom base map with the XYZ standard.
21:49
So yeah but yeah I can show you later where you can do this in the real documentation like in the real configuration so it's definitely possible yeah. Okay thank you.
22:04
And we had one more in the third row. Yeah it will be a quick one. Is it possible to handle a table views and materialized views in PG feature serve? Yeah you can also have views as to publish.
22:23
Okay I have one because I am a member of the OGC the standards body and I have worked with several OGC standards and so on. So I wanted to ask you if PG feature serve or any other lightweight servers are they have they pass all the conformance tests? Have you actually tested them with several other standard clients?
22:47
Honestly I don't know. So but it would be actually a very good thing to do this because you could probably also do this in an automated way that always when there is a new commit or a new release that you directly do a conformance test.
23:02
Yeah good inspiration thanks for the tip.