This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch search-osm-postgres in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit 0f0c4a238c9c890e2837280ad9ee8b294caef0a5 Author: Bertil Chapuis <[email protected]> AuthorDate: Wed Jan 3 00:40:04 2024 +0100 Add a search service for the database --- .../java/org/apache/baremaps/cli/map/Serve.java | 3 + .../org/apache/baremaps/server/SearchResource.java | 96 ++++++++++++++++++++++ basemap/import.js | 6 ++ basemap/queries/{osm_ways.sql => osm_entities.sql} | 12 ++- basemap/queries/osm_nodes.sql | 1 + basemap/queries/osm_relations.sql | 1 + basemap/queries/osm_ways.sql | 1 + 7 files changed, 118 insertions(+), 2 deletions(-) diff --git a/baremaps-cli/src/main/java/org/apache/baremaps/cli/map/Serve.java b/baremaps-cli/src/main/java/org/apache/baremaps/cli/map/Serve.java index a1a0f1bd..d3163566 100644 --- a/baremaps-cli/src/main/java/org/apache/baremaps/cli/map/Serve.java +++ b/baremaps-cli/src/main/java/org/apache/baremaps/cli/map/Serve.java @@ -26,6 +26,7 @@ import io.servicetalk.http.router.jersey.HttpJerseyRouterBuilder; import java.nio.file.Path; import java.util.concurrent.Callable; import java.util.function.Supplier; +import javax.sql.DataSource; import org.apache.baremaps.cli.Options; import org.apache.baremaps.config.ConfigReader; import org.apache.baremaps.server.*; @@ -101,6 +102,7 @@ public class Serve implements Callable<Integer> { .register(TileResource.class) .register(StyleResource.class) .register(TileJSONResource.class) + .register(SearchResource.class) .register(ClassPathResource.class) .register(newContextResolver(objectMapper)) .register(new AbstractBinder() { @@ -108,6 +110,7 @@ public class Serve implements Callable<Integer> { protected void configure() { bind("assets").to(String.class).named("directory"); bind("server.html").to(String.class).named("index"); + bind(datasource).to(DataSource.class); bind(tileStoreSupplier).to(tileStoreSupplierType); bind(styleSupplier).to(styleSupplierType); bind(tileJSONSupplier).to(tileJSONSupplierType); diff --git a/baremaps-server/src/main/java/org/apache/baremaps/server/SearchResource.java b/baremaps-server/src/main/java/org/apache/baremaps/server/SearchResource.java new file mode 100644 index 00000000..f835164d --- /dev/null +++ b/baremaps-server/src/main/java/org/apache/baremaps/server/SearchResource.java @@ -0,0 +1,96 @@ +/* + * Licensed to the Apache Software Foundation (ASF) under one or more + * contributor license agreements. See the NOTICE file distributed with + * this work for additional information regarding copyright ownership. + * The ASF licenses this file to you under the Apache License, Version 2.0 + * (the "License"); you may not use this file except in compliance with + * the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ + +package org.apache.baremaps.server; + +import com.fasterxml.jackson.core.JsonProcessingException; +import com.fasterxml.jackson.databind.JsonNode; +import com.fasterxml.jackson.databind.ObjectMapper; +import java.sql.SQLException; +import java.util.ArrayList; +import java.util.List; +import javax.inject.Inject; +import javax.inject.Singleton; +import javax.sql.DataSource; +import javax.ws.rs.DefaultValue; +import javax.ws.rs.GET; +import javax.ws.rs.Produces; +import javax.ws.rs.QueryParam; +import javax.ws.rs.core.MediaType; +import javax.ws.rs.core.Response; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; + +/** + * A resource that searches for entities in the database. + */ +@Singleton [email protected]("/") +public class SearchResource { + + private static final Logger logger = LoggerFactory.getLogger(SearchResource.class); + + private final String SEARCH_QUERY = + """ + SELECT id, tags, st_asewkt(st_transform(geom, 'EPSG:4326')), ts_rank_cd(to_tsvector('english', tags), query) as rank + FROM osm_entities, phraseto_tsquery('english', ?) as query + WHERE to_tsvector('english', tags) @@ query + ORDER BY rank DESC + LIMIT ?; + """; + + private final DataSource dataSource; + + @Inject + public SearchResource(DataSource dataSource) { + this.dataSource = dataSource; + } + + record SearchResponse(List<SearchResult> results) { + } + + record SearchResult(long id, JsonNode tags, String wkt, double score) { + } + + @GET + @javax.ws.rs.Path("/api/search") + @Produces(MediaType.APPLICATION_JSON) + public Response search( + @QueryParam("query") String queryText, + @QueryParam("limit") @DefaultValue("10") int limit) { + try (var connection = dataSource.getConnection()) { + var statement = connection.prepareStatement(SEARCH_QUERY); + statement.setString(1, queryText); + statement.setInt(2, limit); + var result = statement.executeQuery(); + var list = new ArrayList<SearchResult>(); + while (result.next()) { + var id = result.getLong(1); + var json = result.getString(2); + var tags = new ObjectMapper().readTree(json); + var wkt = result.getString(3); + var rank = result.getDouble(4); + list.add(new SearchResult(id, tags, wkt, rank)); + } + var response = new SearchResponse(list); + return Response.status(Response.Status.OK).entity(response).build(); + } catch (SQLException | JsonProcessingException e) { + logger.error("Error while searching for {}", queryText, e); + return Response.status(Response.Status.INTERNAL_SERVER_ERROR).build(); + } + } +} diff --git a/basemap/import.js b/basemap/import.js index 93054a34..f5490ae7 100644 --- a/basemap/import.js +++ b/basemap/import.js @@ -122,6 +122,12 @@ export default { "databaseSrid": 3857, "replaceExisting": true, }, + { + "type": "ExecuteSql", + "file": "queries/osm_entities.sql", + "database": config.database, + "parallel": false, + }, ] }, { diff --git a/basemap/queries/osm_ways.sql b/basemap/queries/osm_entities.sql similarity index 76% copy from basemap/queries/osm_ways.sql copy to basemap/queries/osm_entities.sql index 54ae022c..7349c81c 100644 --- a/basemap/queries/osm_ways.sql +++ b/basemap/queries/osm_entities.sql @@ -13,5 +13,13 @@ -- See the License for the specific language governing permissions and -- limitations under the License. -CREATE INDEX IF NOT EXISTS osm_ways_geom_index ON osm_ways USING gist (geom); -CREATE INDEX IF NOT EXISTS osm_ways_tags_index ON osm_ways USING gin (tags); \ No newline at end of file +CREATE VIEW osm_entities AS ( + SELECT 'node' as type, id, tags, geom + FROM osm_nodes + UNION + SELECT 'way' as type, id, tags, geom + FROM osm_ways + UNION + SELECT 'relation' as type, id, tags, geom + FROM osm_relations +); diff --git a/basemap/queries/osm_nodes.sql b/basemap/queries/osm_nodes.sql index 882d5e4a..290b84b2 100644 --- a/basemap/queries/osm_nodes.sql +++ b/basemap/queries/osm_nodes.sql @@ -14,4 +14,5 @@ -- limitations under the License. CREATE INDEX IF NOT EXISTS osm_nodes_tags_index ON osm_nodes USING gin (tags); +CREATE INDEX IF NOT EXISTS osm_nodes_tags_tsvector_index ON osm_nodes USING gin (to_tsvector('english', tags)); CREATE INDEX IF NOT EXISTS osm_nodes_geom_index ON osm_nodes USING gist (geom); diff --git a/basemap/queries/osm_relations.sql b/basemap/queries/osm_relations.sql index a1711bea..ceae0432 100644 --- a/basemap/queries/osm_relations.sql +++ b/basemap/queries/osm_relations.sql @@ -14,4 +14,5 @@ -- limitations under the License. CREATE INDEX IF NOT EXISTS osm_relations_tags_index ON osm_relations USING gin (tags); +CREATE INDEX IF NOT EXISTS osm_relations_tags_tsvector_index ON osm_relations USING gin (to_tsvector('english', tags)); CREATE INDEX IF NOT EXISTS osm_relations_geom_index ON osm_relations USING gist (geom); diff --git a/basemap/queries/osm_ways.sql b/basemap/queries/osm_ways.sql index 54ae022c..6e707f39 100644 --- a/basemap/queries/osm_ways.sql +++ b/basemap/queries/osm_ways.sql @@ -14,4 +14,5 @@ -- limitations under the License. CREATE INDEX IF NOT EXISTS osm_ways_geom_index ON osm_ways USING gist (geom); +CREATE INDEX IF NOT EXISTS osm_ways_tags_tsvector_index ON osm_ways USING gin (to_tsvector('english', tags)); CREATE INDEX IF NOT EXISTS osm_ways_tags_index ON osm_ways USING gin (tags); \ No newline at end of file
