Spatial Queries

Spatial Queries

The /geodata web call allows making basic spatial queries for fixed stations. It uses the open-source Java GIS toolkit JTS (Java Topology Suite) or the PostGIS extension to Postgres. Result is returned as comma-separated values. It allows composing the tables corresponding to stations in columns or in rows. For moving stations, see Dynamic Spatial Queries.

To select which engine to use for the spatial queries, you need to edit the file conf/spatial.properties.

# File conf/spatial.properties
# Example of use with JTS (JTS Topology Suite). No other parameters needed.
type=jts

# File conf/spatial.properties
# Example of use with PostGIS
type=postgis
dburl=jdbc:postgresql://localhost:5432/postgis
dbuser=postgres
dbpass=postgres 

1. Composing columns

Parameters:

• Env: geometric envelope specified in Well Known Text WKT format. This envelope is used to create the list of stations falling within the envelope. This list can be reused in the query, using the keyword $sensors.
• Query: an SQL query where the reserved keyword $sensors represents the list of stations within the envelope. GSN reformats the query by replacing the keyword with the list of relevant stations.

Example: We need to read all stations sensors, for stations that are within the area defined by the polygon

• Env: POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))
• Query: select * from $sensors limit 0,1

http://localhost:22001/geodata?query=select * from $sensors limit 0,1&env=POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))

Result:

# Query: select * from stations limit 0,1
# Reformatted query: select * from  station_41, station_42, station_43 limit 0,1
# pk,timed,wind_direction,wind_speed,rain_meter,solar_rad,soil_water_potential,soil_moisture_ectm,soil_temp_ectm,air_humid,air_temp,air_temp_tnx,ground_temp_tnx,pk,timed,wind_direction,wind_speed,rain_meter,solar_rad,soil_water_potential,soil_moisture_ectm,soil_temp_ectm,air_humid,air_temp,air_temp_tnx,ground_temp_tnx,pk,timed,wind_direction,wind_speed,rain_meter,solar_rad,soil_water_potential,soil_moisture_ectm,soil_temp_ectm,air_humid,air_temp,air_temp_tnx,ground_temp_tnx
1,1247590500000,126.505,5.767,0.0,662.411,1945.05,29.456,11.4,69.114,15.76,16.35,17.975,1,1244647800000,106.725,3.604,0.0,820.337,2015.87,48.488,7.1,45.016,10.62,11.975,17.1,1,1245477300000,149.626,0.084,0.0,25.882,2028.08,49.008,9.7,64.506,1.73,1.6

2. Composing Rows

For some queries, we need to compose rows using unions, which results in tables being combined vertically. This is particularly needed for aggregations across stations.

Parameters:

• Env: geometric envelope specified in Well Known Text WKT format. This envelope is used to create the list of stations falling within the envelope. This list can be reused in the query, using the keyword $sensors.
• Query: an SQL query where the reserved keyword $sensors represents the list of stations within the envelope. GSN reformats the query by replacing the keyword with the relevant stations.
• Union: an SQL query to be composed through unions. In each query, the reserved keyword $sensor will be iteratively replaced by a station within the envelope.

Here are a few examples:

2.1 Showing for each station in a given area, the wind speed for a given time

The envelope determines the stations enclosed in the area (as in composing columns).

• Envelope: POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))
• Query: select * from ($union) as result
• Union: select "$sensor" as station, wind_speed from $sensor where timed=1247590500000

The resulting query will be:

select * from 
(
select "station_40" as station, wind_speed from station_40 where timed=1247590500000
union
select "station_41" as station, wind_speed from station_41 where timed=1247590500000
union
select "station_42" as station, wind_speed from station_42 where timed=1247590500000
) as result

Result:

station	wind_speed
station_40	4.593
station_41	5.767
station_42	5.281

2.2 Calculating the average wind speed in a given area for a given time

• Env: POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))
• Query: select avg(wind_speed) from ($union) as result
• Union: select wind_speed from $sensor where timed=1247590500000

The resulting query will be:

select avg(wind_speed) from 
(
select wind_speed from station_40 where timed=1247590500000
union
select wind_speed from station_41 where timed=1247590500000
union
select wind_speed from station_42 where timed=1247590500000
) as result 

Result:

avg(wind_speed)
5.66667

2.3 Finding the stations within the area, for which the wind speed has been higher than 15m/s and showing the corresponding speed and date, ordering the result by wind speed

• Env: POLYGON ((0 0, 0 100, 100 100, 100 0, 0 0))
• Query: select station, wind_speed, date from ($union) as result where wind_speed>15 order by wind_speed
• Union: "$sensor" as station, wind_speed, from_unixtime(timed/1000) as date from $sensor

The resulting query will be:

select station, wind_speed, date from 
(
select "station_40" as station, wind_speed, from_unixtime(timed/1000) as date from  station_40
union
select "station_41" as station, wind_speed, from_unixtime(timed/1000) as date from  station_41
union
select "station_42" as station, wind_speed, from_unixtime(timed/1000) as date from  station_42
) as result where wind_speed>15 
order by wind_speed

Result:

station	wind_speed	date
station_42	15.003	2009-07-22 09:10:00
station_42	15.037	2009-07-22 08:55:00
station_42	15.087	2009-09-03 11:40:00
station_42	15.221	2009-07-22 08:45:00
station_42	15.238	2009-09-03 03:40:00
station_42	15.389	2009-07-22 08:35:00
station_42	15.858	2009-07-22 09:40:00
station_42	16.194	2009-07-23 03:55:00
station_42	16.361	2009-09-16 06:35:00
station_42	16.495	2009-09-15 10:10:00
station_42	16.646	2009-09-15 20:30:00
station_42	16.646	2009-09-15 20:50:00
station_42	16.982	2009-09-15 20:45:00
station_42	18.054	2009-09-15 20:55:00
station_42	18.272	2009-09-15 20:35:00