Hologres:Query spatial data with PostGIS

Prerequisites

Before you begin, ensure that you have:

• A Hologres instance. See Purchase a Hologres instance

• A database in your Hologres instance. See Create a database

• The sample data files downloaded:

  • accommodations.csv

  • zipcode.csv

Coordinate systems and SRID

Every PostGIS geometry object is associated with a Spatial Reference Identifier (SRID) that defines the coordinate system it uses. All geometry objects in a query must share the same SRID to produce correct results.

This tutorial uses SRID 4326, which corresponds to WGS 84 — the coordinate system used by GPS and most web mapping services, where coordinates are expressed as longitude and latitude in decimal degrees. Both sample datasets use SRID 4326, so they are directly interoperable in spatial queries.

The sample data contains Berlin accommodation listings (accommodations) and Berlin postal code boundaries (zipcode).

Step 1: Create tables

Create the accommodations and zipcode tables in your Hologres database.

1. Log on to the HoloWeb console SQL Editor and open the SQL Editor.

2. Click the icon below the SQL Editor tab to open the Ad-hoc Query window. In the toolbar, select your Instance Name and Database.

3. Load the PostGIS extension by running the following SQL command:

   CREATE EXTENSION IF NOT EXISTS postgis; -- Load the PostGIS extension

4. Create the accommodations table, which stores accommodation locations (longitude and latitude), listing names, and related data:

   CREATE TABLE public.accommodations (
     id INTEGER PRIMARY KEY,
     shape GEOMETRY,
     name VARCHAR(100),
     host_name VARCHAR(100),
     neighbourhood_group VARCHAR(100),
     neighbourhood VARCHAR(100),
     room_type VARCHAR(100),
     price SMALLINT,
     minimum_nights SMALLINT,
     number_of_reviews SMALLINT,
     last_review DATE,
     reviews_per_month NUMERIC(8,2),
     calculated_host_listings_count SMALLINT,
     availability_365 SMALLINT
   );

   After the table is created, click the icon next to Table Directory on the left, then click public > Table to confirm the table was created. Alternatively, check the Operational Logs.

5. Create the zipcode table, which stores Berlin postal code boundary data:

   CREATE TABLE public.zipcode (
     ogc_field INTEGER PRIMARY KEY NOT NULL,
     wkb_geometry GEOMETRY,
     gml_id VARCHAR(256),
     spatial_name VARCHAR(256),
     spatial_alias VARCHAR(256),
     spatial_type VARCHAR(256)
   );

   After the table is created, click the icon next to Table Directory on the left, then click public > Table to confirm the table was created. Alternatively, check the Operational Logs.

Step 2: Import test data

Use the Import On-premises File feature in HoloWeb to load data into both tables. Repeat the following steps for each file.

1. In the HoloWeb consoleHoloWeb console, click Data Solutions at the top.

2. On the Data Solutions page, click Import On-premises File on the left, then click New Data Import on the right.

3. In the Import On-premises File dialog box, enter a job name and select your instance, database, and target table (accommodations or zipcode). Click Next.

   

4. On the Upload File tab, configure the following settings, then click Next.

   Parameter	Description
   Select file	Click Browse and select the .csv file. Supported formats: .txt, .csv, .log.
   Select separator	Select Semicolon. To use a custom separator, click the option next to the separator field.
   Source character set	Select UTF-8.
   First row as header	Leave this blank unless the first row of your file contains column headers.

   

5. On the Import Overview tab, verify the settings, then click Upload.

   

6. After the import completes, the system shows whether it succeeded. If it failed, the system shows the reason — fix the issue and reimport.

Verify the import. Run the following queries to check record counts:

SELECT COUNT(*) FROM accommodations; -- Expected: 22,248
SELECT COUNT(*) FROM zipcode;        -- Expected: 190

To inspect the data directly:

SELECT * FROM accommodations;
SELECT * FROM zipcode;

Step 3: Query data using spatial functions

With both tables loaded, run spatial queries using PostGIS functions. The examples below cover the most common patterns. For full function syntax, see Spatial functions.

How many accommodations have valid WGS 84 coordinates?

SELECT COUNT(*) FROM public.accommodations WHERE ST_SRID(shape) = 4326;

Result:

 count
-------
 22248
(1 row)

Do the postal code boundaries also use SRID 4326?

Run ST_SRID on the zipcode table to confirm both datasets share the same coordinate system before running any joins or distance queries.

SELECT  ogc_field,
        spatial_name,
        spatial_type,
        ST_SRID(wkb_geometry),
        ST_AsText(wkb_geometry)
FROM    public.zipcode
ORDER BY spatial_name;

Result (190 rows):

ogc_field  spatial_name  spatial_type  st_srid  st_astext
-------------------------------------------------------------
0           10115        Polygon        4326     POLYGON((...))
4           10117        Polygon        4326     POLYGON((...))
8           10119        Polygon        4326     POLYGON((...))
...

Spatial data must use the same spatial reference system to be interoperable.

What is the geometry of the Berlin Mitte postal code area (10117)?

Use ST_AsGeoJSON to get the boundary in GeoJSON format, and ST_Dimension and ST_NPoints to inspect its structure.

SELECT  ogc_field,
        spatial_name,
        ST_AsGeoJSON(wkb_geometry),
        ST_Dimension(wkb_geometry),
        ST_NPoints(wkb_geometry)
FROM    public.zipcode
WHERE   spatial_name = '10117';

Result:

ogc_field  spatial_name  st_asgeojson                                   st_dimension  st_npoints
-------------------------------------------------------------------------------------------------
4           10117         {"type":"Polygon", "coordinates":[[[...]]]}    2             331

How many accommodations are within 500 meters of the Brandenburg Gate?

ST_DistanceSphere calculates the distance between two points on the Earth's surface. ST_DistanceSphere is appropriate for geographic coordinates (longitude/latitude) and returns the result in meters. The Brandenburg Gate is at coordinates POINT(13.377704 52.516431) (SRID 4326).

SELECT COUNT(*)
FROM   public.accommodations
WHERE  ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500;

Result:

 count
-------
    29
(1 row)

How many accommodations are within 500 meters of listings near the Brandenburg Gate?

This query first finds the location of accommodations whose name mentions "Brandenburg Gate", then counts all accommodations within 500 meters of those locations.

WITH poi(loc) AS (
    SELECT st_astext(shape)
    FROM   accommodations
    WHERE  name LIKE '%brandenburg gate%'
)
SELECT COUNT(*)
FROM   accommodations a,
       poi p
WHERE  ST_DistanceSphere(a.shape, ST_GeomFromText(p.loc, 4326)) < 500;

Result:

 count
-------
    60
(1 row)

Which accommodations near the Brandenburg Gate are the most expensive?

SELECT  name,
        price,
        ST_AsText(shape)
FROM    public.accommodations
WHERE   ST_DistanceSphere(shape, ST_GeomFromText('POINT(13.377704 52.516431)', 4326)) < 500
ORDER BY price DESC;

Result (29 rows):

name                                              | price |               st_astext
--------------------------------------------------+-------+------------------------------------------
DUPLEX APARTMENT/PENTHOUSE in 5* LOCATION! 7583   |   300 | POINT(13.3826510209548 52.5159819722552)
DUPLEX-PENTHOUSE IN FIRST LOCATION! 7582          |   300 | POINT(13.3799997083855 52.5135918444834)
Luxury Apartment in Berlin Mitte with View        |   259 | POINT(13.3835653528534 52.516360156825)
BIG APT 4 BLNCTY-CNTR 43-H6                       |   240 | POINT(13.3800222998777 52.5134224506894)
BIG APARTMENT-PRIME LOCATION-BEST PRICE! B0303    |   240 | POINT(13.379745196599 52.5162648947249)
BIG APARTMENT IN BRILLIANT LOCATION-CTY CENTRE B53|   240 | POINT(13.381383105167 52.5157082721072)
SONYCENTER: lux apartment - 3room/2bath. WIFI     |   235 | POINT(13.3743158954191 52.5125308432819)
CENTRE APARTMENT FOR 6 | 8853                     |   220 | POINT(13.3819039478615 52.5134866767369)
BIG APARTMENT FOR 6 - BEST LOCATION 8863          |   209 | POINT(13.3830430841658 52.5147824286783)
3 ROOMS ONE AMAZING EXPERIENCE! 8762              |   190 | POINT(13.3819898503053 52.5144190764637)
AAA LOCATION IN THE CENTRE H681                   |   170 | POINT(13.3821787206534 52.5129769242004)
H672 Nice Apartment in CENTRAL LOCATION!          |   170 | POINT(13.3803137710339 52.5132386929089)
"Best View -best location!"                       |   170 | POINT(13.3799551247135 52.5147888483851)
H652 Best Location for 4!                         |   170 | POINT(13.3805705422409 52.5143845784482)
H651 FIT´s for Four in a 5* Location!             |   150 | POINT(13.3822063502184 52.5134994650996)
NEXT TO ATTRACTIONS! H252                         |   110 | POINT(13.3823616629115 52.5136258446666)
CTY Centre Students Home| G4                      |   101 | POINT(13.3808081476226 52.5130957830586)
Room for two with private shower / WC             |    99 | POINT(13.3786877948382 52.5208018292043)
StudentsHome CityCentre Mitte 91-0703             |    95 | POINT(13.3810390515141 52.5142363781923)
FIRST LOCATION - FAIR PRICE K621                  |    80 | POINT(13.3823909855061 52.5131554670458)
LONG STAY FOR EXPATS/STUDENTS- CITY CENTRE | K921 |    75 | POINT(13.380320945399 52.512364557598)
Nice4Students! City Centre 8732                   |    68 | POINT(13.3810147526683 52.5136623602892)
Comfy Room in the heart of Berlin                 |    59 | POINT(13.3813167311819 52.5127345388756)
FO(U)R STUDENTS HOME-Best centre Location!        |    57 | POINT(13.380850032042 52.5131726958513)
Berlin Center Brandenburg Gate !!!                |    55 | POINT(13.3849641540689 52.5163902851474)
!!! BERLIN CENTER BRANDENBURG GATE                |    55 | POINT(13.379997730927 52.5127577639174)
Superb Double Bedroom in Central Berlin           |    52 | POINT(13.3792991992688 52.5156572293422)
OMG! That's so Berlin!                            |    49 | POINT(13.3754883007165 52.5153487677272)
Apartment in Berlin's old city center             |    49 | POINT(13.3821761577766 52.514037240604)
(29 rows)

Which postal code contains the most expensive accommodation?

ST_Within returns true if geometry g1 is completely inside geometry g2. The query below retrieves the most expensive listing (priced at 9,000) and its postal code boundary.

SELECT  a.price,
        a.name,
        ST_AsText(a.shape),
        z.spatial_name,
        ST_AsText(z.wkb_geometry)
FROM    accommodations a,
        zipcode z
WHERE   price = 9000
AND     ST_Within(a.shape, z.wkb_geometry);

Result:

price   name                              st_astext                                spatial_name  st_astext
---------------------------------------------------------------------------------------------------------
9000    Ueber den Dächern Berlins Zentrum  POINT(13.334436985013 52.4979779501538)  10777         POLYGON((13.3318284987227 52.4956021172799,...

Which postal codes have the most accommodation listings?

Use ST_Within with GROUP BY to aggregate accommodations by postal code — useful for identifying accommodation hot spots across Berlin.

SELECT  z.spatial_name AS zip,
        COUNT(*) AS numAccommodations
FROM    public.accommodations a,
        public.zipcode z
WHERE   ST_Within(a.shape, z.wkb_geometry)
GROUP BY zip
ORDER BY numAccommodations DESC;

Result (187 rows):

zip      numaccommodations
--------------------------
10245    872
10247    832
10437    733
10115    664
...

Spatial functions reference

All spatial functions used in this tutorial are standard PostGIS functions. For the complete function reference, see Spatial functions.

What's next

• Spatial functions — full PostGIS function reference for Hologres