[ https://issues.apache.org/jira/browse/STREAMPIPES-584?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17626561#comment-17626561 ]
Florian Micklich edited comment on STREAMPIPES-584 at 10/31/22 12:26 PM:
-------------------------------------------------------------------------
{code:java}
-------- Ursprüngliche Nachricht --------<br>
Von: Florian Micklich via user <[user@sis.apache.org](Florian%20Micklich%20via%20user%20%3cuser@sis.apache.org%3e)><br>
Antwort an: Florian Micklich <[florian.micklich@mailbox.org](Florian%20Micklich%20%3cflorian.micklich@mailbox.org%3e)><br>
An: Apache SIS <[user@sis.apache.org](Apache%20SIS%20%3cuser@sis.apache.org%3e)><br>
Betreff: manually setup an EPSG database base information<br>
Datum: Sat, 29 Oct 2022 23:05:21 +0200<br>
Hello everyone,<br>
I want to manually setup an EPSG database like mentioned here [1]
and need some more understanding what happens under the hood and how to implement it correctly.<br>
I set up the database configuration in my app. How is the connection build up to this container?
After configuration, do I have to open & close the connection?<br>
Therefore I use a local docker container with an postgres image after downloading the EPSG-v9_9_1-PostgreSQL data:<br>```
version: "3"
services:
epsg:
image: "postgres:14.4-alpine"
container_name: "epsg"
ports:
"5432:5432"
environment:
POSTGRES_DB: EPSG
POSTGRES_USER: epsg
POSTGRES_PASSWORD: epsg
volumes:
./scripts/PostgreSQL_Table_Script.sql:/docker-entrypoint-initdb.d/01_PostgreSQL_Table_Script.sql ./scripts/PostgreSQL_Data_Script.sql:/docker-entrypoint-initdb.d/02_PostgreSQL_Data_Script.sql ./scripts/PostgreSQL_FKey_Script.sql:/docker-entrypoint-initdb.d/03_PostgreSQL_FKey_Script.sql epsg-data:/var/lib/postgresql/data
volumes:
epsg-data:
```
In my Java App I setup everything:<br>```
import org.postgresql.ds.PGSimpleDataSource;
import org.apache.sis.setup.Configuration;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.function.Supplier;
...
Configuration.current().setDatabase((Supplier<DataSource>) createDataSource());
...
protected static DataSource createDataSource() {
PGSimpleDataSource ds = new PGSimpleDataSource();
String[] serverAddresses =
{ "192.168.1.100" }
;
ds.setServerNames( serverAddresses );
int[] serverPortNumbers =
{ 5432 }
;
ds.setPortNumbers( serverPortNumbers );
ds.setDatabaseName( "EPSG" );
ds.setUser( "epsg" );
ds.setPassword( "epsg" );
ds.setReadOnly( true );
return ds;
}
```
Greetings
Florian<br>
[1] https://sis.apache.org/epsg.html<br>
{code}
was (Author: micklich):
-------- Ursprüngliche Nachricht --------<br>
Von: Florian Micklich via user <[user@sis.apache.org](mailto:Florian%20Micklich%20via%20user%20%3cuser@sis.apache.org%3e)><br>
Antwort an: Florian Micklich <[florian.micklich@mailbox.org](mailto:Florian%20Micklich%20%3cflorian.micklich@mailbox.org%3e)><br>
An: Apache SIS <[user@sis.apache.org](mailto:Apache%20SIS%20%3cuser@sis.apache.org%3e)><br>
Betreff: manually setup an EPSG database base information<br>
Datum: Sat, 29 Oct 2022 23:05:21 +0200<br>
Hello everyone,<br>
I want to manually setup an EPSG database like mentioned here [1]
and need some more understanding what happens under the hood and how to implement it correctly.<br>
I set up the database configuration in my app. How is the connection build up to this container?
After configuration, do I have to open & close the connection?<br>
Therefore I use a local docker container with an postgres image after downloading the EPSG-v9_9_1-PostgreSQL data:<br>```
version: "3"
services:
epsg:
image: "postgres:14.4-alpine"
container_name: "epsg"
ports:
- "5432:5432"
environment:
POSTGRES_DB: EPSG
POSTGRES_USER: epsg
POSTGRES_PASSWORD: epsg
volumes:
- ./scripts/PostgreSQL_Table_Script.sql:/docker-entrypoint-initdb.d/01_PostgreSQL_Table_Script.sql
- ./scripts/PostgreSQL_Data_Script.sql:/docker-entrypoint-initdb.d/02_PostgreSQL_Data_Script.sql
- ./scripts/PostgreSQL_FKey_Script.sql:/docker-entrypoint-initdb.d/03_PostgreSQL_FKey_Script.sql
- epsg-data:/var/lib/postgresql/data
volumes:
epsg-data:
```
In my Java App I setup everything:<br>```
import org.postgresql.ds.PGSimpleDataSource;
import org.apache.sis.setup.Configuration;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.function.Supplier;
...
Configuration.current().setDatabase((Supplier<DataSource>) createDataSource());
...
protected static DataSource createDataSource() {
PGSimpleDataSource ds = new PGSimpleDataSource();
String[] serverAddresses = { "192.168.1.100" };
ds.setServerNames( serverAddresses );
int[] serverPortNumbers = { 5432 };
ds.setPortNumbers( serverPortNumbers );
ds.setDatabaseName( "EPSG" );
ds.setUser( "epsg" );
ds.setPassword( "epsg" );
ds.setReadOnly( true );
return ds;
}
```
Greetings
Florian<br>
[1] https://sis.apache.org/epsg.html<br>
> Implementing possibility for reprojection Coordinates
> -----------------------------------------------------
>
> Key: STREAMPIPES-584
> URL: https://issues.apache.org/jira/browse/STREAMPIPES-584
> Project: StreamPipes
> Issue Type: New Feature
> Components: Backend, Pipeline Elements
> Reporter: Florian Micklich
> Assignee: Florian Micklich
> Priority: Major
> Attachments: .env, docker-compose.yml, settings_epsg.png, workflow.png, workflow_v2.png
>
>
> h1. Goal:
> Essential Task in Geo is to reproject between different Coordinate Reference Systems (CRS)
> e.g for special calculations which needs meter as CRS Unit instead of degree (as it it in WGS84)
> h1. Libs and Sources:
> Lib for reprojection would be
> https://sis.apache.org/
> Necessary extra package is the EPSG Library Source, which needs a special license agreement (not apache license).
> User Downloads the Postgres SQL Scripts here: https://epsg.org/auth/Account/Login
> During registration User agrees to license, so it can be used in StreamPipes.
> h1.
> h1. Following Workflow (to be discussed)
> The User unzipped the Scripts and but them into a specific folder.
> Then StreamPipes can start the docker-compose file and here the questions occurs.
> how to implement the service and where
> What happens if files are missing
> Where to register the connectionSource and how to validate?
> What happens if setup fails. Are geo processors active or special Exceptions during Pipeline Creation?
> !workflow_v2.png!
> Draft for EPSG Database Setup:
> !settings_epsg.png!
> h1. Material
> Docker-compose file for SP Service
> [^.env]
> [^docker-compose.yml]
--
This message was sent by Atlassian Jira
(v8.20.10#820010)