Michael Diener mdiener21

## select_polys_with_points_inside.sql
 SELECT DISTINCT s.id, s.tags
      FROM myspace_polys as s
      JOIN mypoi_points as as p
      ON ST_Contains(s.geom, p.geom);

## run_postgis_docker.sh
$ docker run --name=postgis -d -e POSTGRES_USER=user001 -e POSTGRES_PASS=123456789 -e POSTGRES_DBNAME=gis -p 5432:5432 kartoza/postgis:9.6-2.4

## install-gdal.sh
sudo apt install python3-gdal gdal-bin
pip install --global-option=build_ext --global-option="-I/usr/include/gdal" GDAL==`gdal-config --version`

## round_nearest_quarter_hour
# sheets formula to round the value in cell F2 to the nearest quarter hour
=HOUR(F2) + (MROUND(MINUTE(F2),15)/60)

if 1:18:33 is the duration the result is 1.25.

## test-data.geojson

      
              1 file
            
          
              0 forks
            
          
              0 comments
            
          
              0 stars
            
          
                mdiener21
                / test-data.geojson
            
            
              Created
              November 29, 2018 14:51
            
              
                test project data
              
          
      Sorry, something went wrong. Reload?
      Sorry, we cannot display this file.
      Sorry, this file is invalid so it cannot be displayed.
      
          Viewer requires iframe.
      
    
## select_polys_inside.sql
SELECT c.cadaster_id, r.name
 , CASE
   WHEN ST_CoveredBy(c.geom, r.geom)
   THEN c.geom
   ELSE
    ST_Multi(
      ST_Intersection(c.geom,r.geom)
      ) END AS geom
 FROM cadaster AS c
   INNER JOIN roads_ply AS r

## buffer_with_dissolve.sql
-- create a 0.5m buffer with square edges using mitre join
-- used for buffering building spaces to create a floor space
-- only taking 5 ids
SELECT fk_building_id, fk_building_floor_id,
st_multi(ST_Union(St_Buffer(b.geom,0.5,'endcap=square join=mitre mitre_limit=2')))
 AS geom FROM django.buildings_buildingfloorspace AS b
 WHERE floor_num = 1 AND fk_building_id IN (2,3,6,7,8)
 GROUP BY fk_building_id, fk_building_floor_id

## geoserver-cors.xml
<filter>
  <filter-name>CorsFilter</filter-name>
  <filter-class>org.apache.catalina.filters.CorsFilter</filter-class>
  <init-param>
    <param-name>cors.allowed.origins</param-name>
    <param-value>*</param-value>
  </init-param>
  <init-param>
    <param-name>cors.allowed.methods</param-name>
    <param-value>GET,POST,HEAD,OPTIONS,PUT</param-value>

## django-centroid.py
import json
from geojson import Feature
from django.contrib.gis.db.models.functions import Centroid, AsGeoJSON
from buildings.models import Space

from rest_framework.decorators import api_view
from rest_framework.response import Response

@api_view(['GET'])
def get_room_center(request, unique_id):

## openlayers_stacked_style.js
// using font awesome 5 free version
var faFlagSolidStyle = new ol.style.Style({
  text: new ol.style.Text({
    text: '\uf024', // fas flag solid
    scale: 1,
    font: 'normal 18px FontAwesome',
    offsety: -30,
    offsetx: -10,
    fill: new ol.style.Fill({color: 'black'}),
  })
	SELECT DISTINCT s.id, s.tags
	FROM myspace_polys as s
	JOIN mypoi_points as as p
	ON ST_Contains(s.geom, p.geom);
	sudo apt install python3-gdal gdal-bin
	pip install --global-option=build_ext --global-option="-I/usr/include/gdal" GDAL==`gdal-config --version`
	# sheets formula to round the value in cell F2 to the nearest quarter hour
	=HOUR(F2) + (MROUND(MINUTE(F2),15)/60)

	if 1:18:33 is the duration the result is 1.25.
	SELECT c.cadaster_id, r.name
	, CASE
	WHEN ST_CoveredBy(c.geom, r.geom)
	THEN c.geom
	ELSE
	ST_Multi(
	ST_Intersection(c.geom,r.geom)
	) END AS geom
	FROM cadaster AS c
	INNER JOIN roads_ply AS r
	-- create a 0.5m buffer with square edges using mitre join
	-- used for buffering building spaces to create a floor space
	-- only taking 5 ids
	SELECT fk_building_id, fk_building_floor_id,
	st_multi(ST_Union(St_Buffer(b.geom,0.5,'endcap=square join=mitre mitre_limit=2')))
	AS geom FROM django.buildings_buildingfloorspace AS b
	WHERE floor_num = 1 AND fk_building_id IN (2,3,6,7,8)
	GROUP BY fk_building_id, fk_building_floor_id
	<filter>
	<filter-name>CorsFilter</filter-name>
	<filter-class>org.apache.catalina.filters.CorsFilter</filter-class>
	<init-param>
	<param-name>cors.allowed.origins</param-name>
	<param-value>*</param-value>
	</init-param>
	<init-param>
	<param-name>cors.allowed.methods</param-name>
	<param-value>GET,POST,HEAD,OPTIONS,PUT</param-value>
	import json
	from geojson import Feature
	from django.contrib.gis.db.models.functions import Centroid, AsGeoJSON
	from buildings.models import Space

	from rest_framework.decorators import api_view
	from rest_framework.response import Response

	@api_view(['GET'])
	def get_room_center(request, unique_id):
	// using font awesome 5 free version
	var faFlagSolidStyle = new ol.style.Style({
	text: new ol.style.Text({
	text: '\uf024', // fas flag solid
	scale: 1,
	font: 'normal 18px FontAwesome',
	offsety: -30,
	offsetx: -10,
	fill: new ol.style.Fill({color: 'black'}),
	})