About me

Hi, my name is

Benoit Perceval

, I'm GIS Expert / DB Administrator in

Conservatoire d'espaces naturels Normandie Ouest

. I'm attracted to everything related to geographic information :

  • Geospatial Database
  • Geostatistic application
  • Developement
  • GIS Software

If you want any further information about me, do not hesitate to contact me.

benoit perceval ©

Leaflet & Autocomplete

Leaflet / Autocomplete

Leaflet Map with custom pop-up, jquery-ui autocomplete.


Autocomplete Liste Select

2015

A simple map with Autocomplete and search by ID

A Use Case with PostGIS and Leaflet

PostGIS / Leaflet

Advantage: No GeoJSON file used, All PostGIS functions can be used in the AJAX queries (Reprojection, Bounds, etc.).


Leaflet / GGChart Leaflet / legend Leaflet / fond

2013

A simple map with discretized data

Europe Evolution :

UE evolution

2008

Evolution of the German population :

German evolution

2008

Export/Transform your *.shp, *.tab, *.json File with ogr2ogr

ogr2ogr export transform

2011

Here you can reproject / export your file with ogr2ogr. You just need to zip your input file. Choose an export format and get your output file.

TXT_2_PG is a freeware which allows you to load a set of *.txt files into PostgreSQL

TXT_2_PG

2011

Download *.exe file

Source Code *.py files

Version : 1.4

TXT_2_PG is a freeware which allows you to load a set of *.txt files into PostgreSQL. A SQLite DB is created when you run txt_2_pg for the first time. You must have logically the write permission on your local disk


Documentation

My work at Articque Solutions :

Between 10-04-2011 and 10-10-2014, I worked at Articque Solutions.
First as a GIS specialist and then as a project manager. I went through many tasks, I assured the training PostgreSQL, Cartes & Données 6.x and MapInfo 10.x . I have also produced many prototypes for pre-sales or consulting. My missions were very different I was in constant contact with the development team, the sales team and customers. I was responsible for the production of cartographic atlas and content databases. SSII for projects, according to customer requests I established technical specifications and realized a cipher for the completion time.

ANTI-JOIN (Faster than NOT IN) :


-- ANTI JOIN
-- visites_iris_2011 > 1400000 entities | iris_centre > 1100000 entities
-- execution time : 39 sec 
SELECT ic.iris_centre  from iris_centre ic where 
NOT EXISTS 
	(SELECT distinct(id_iris_centre) 
	FROM visites_iris_20XX vi 
	WHERE ic.iris_centre = vi.id_iris_centre);

-- execution time : stopped after 1h30
select iris_centre.iris_centre  from iris_centre 
where iris_centre 
NOT IN (select distinct(id_iris_centre) from visites_iris_20XX);
                    

Function to Drop all tables in a schema


CREATE OR REPLACE FUNCTION drop_table(schema_name varchar(100))
RETURNS bool AS
 $BODY$
 DECLARE
   rec_selection record;
    BEGIN
      FOR rec_selection IN (SELECT table_name FROM information_schema.TABLES WHERE table_schema=schema_name AND table_type='BASE TABLE') LOOP
        EXECUTE 'DROP TABLE schema_name.'|| rec_selection.table_name ||'   ';
      END LOOP;
    RETURN True;
   END;
 $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
-- Then execute function
SELECT drop_table('my_schema');
                    

Cleaning/Correct overlapping polygons in a PostGIS table

I start with 1 table : 'test'. This table contains overlapping polygons :

polygons

A) Get intersection union :


--create all intersections with table test
DROP TABLE IF EXISTS intersection;
SELECT a.initial_id as id_a, b.initial_id as id_b, st_intersection(a.geom, b.geom) AS geom_intersection
INTO intersection 
FROM test a, test b;

--delete auto_intersection with id (initial_id)
DELETE FROM intersection WHERE id_a = id_b;

-- create table intersection_union 
DROP TABLE IF EXISTS intersection_union;
SELECT st_union(geom_intersection) AS geom INTO intersection_union FROM intersection
                    

result :

polygons

B) Get Difference :


-- get difference 
DROP TABLE IF EXISTS difference;
SELECT st_difference(a.geom, b.geom) AS geom_difference
INTO difference 
FROM test a, intersection_union b ;
                    

result :

polygons

C) Get final layer :


--create the final geom with difference and intersection_union
DROP TABLE IF EXISTS final_shape;
CREATE TABLE final_shape AS
(
select  st_union(iu.geom) AS geom FROM intersection_union iu
UNION
select d.geom_difference as geom FROM difference d
);
                    

result :

polygons

2012

Cleaning/Correct overlapping polygons in a PostGIS table

A Shortest version :


--Only one command
CREATE TABLE final_shape AS
WITH 
UNION_ AS
(SELECT  ST_Union(st_intersection(a.geom, b.geom)) AS geom_intersection
FROM test a, test b
WHERE a.id <> b.id), 
DIFFERENCE_ AS
(SELECT ST_DIFFERENCE(geom , geom_intersection) as geom_difference FROM test a, UNION_)
SELECT geom_intersection FROM UNION_
UNION
SELECT geom_difference FROM DIFFERENCE_;
                    

2012, thanks to the "WITH" command

Avoid recursive functions with PostgreSQL v9.x :


-- (only with PostgreSQL v9.0 or more recent)
-- DROP TRIGGER IF EXISTS tg_after_up_date_f_setsrid ON update_id_site;
-- 0 shows whether the update is from a trigger
CREATE TRIGGER tg_after_up_date_f_setsrid
	AFTER INSERT
	ON update_id_site
	FOR EACH ROW
	WHEN (pg_trigger_depth() = 0)
	EXECUTE PROCEDURE f_setsrid_up_date();

UPDATE query with a JOIN and a SUBQUERY :


-- To attribute the first id found in another table
UPDATE table_1 SET id_iris_new = 
	(SELECT f.id_iris 
	FROM fr_iris f
	WHERE f.id_iris 
	like substring(table_1.id_iris from 1 for 5) 
	ORDER BY f.id_iris 
	LIMIT 1) ;
					

DELETE DUPPLICATES

(without DISTINCT which is resource intensive)


DELETE FROM table_one t1
WHERE EXISTS (SELECT * FROM table_one t2 
	WHERE t1.unique_key > t2.unique_key 
	AND t1.xxxx = t2.xxxx
	AND t1.yyyy = t2.yyyy
	AND t1.zzzz = t2.zzzz
	AND t1. ... = t2. ...
	...	-- all dupplicate fields
	);
-- ">" will keep the first line
-- or use another operator

You can create a primary key with a postgres sequence and 'nextval' function:
CREATE SEQUENCE my_sequence START 1;
SELECT nextval('my_sequence');

benoit perceval<\ © >

Database

  • PostgreSQL/PostGIS - SQL Server 2008
  • Server side coding : stored procedures
  • Server side coding : functions, triggers
  • Complex queries
  • Loading or exporting large amounts of data
  • Understanding Indexes as optimization tool
  • Views
mysql postgresql sqlserver database

GIS Software / Webmapping

  • Quantum GIS 2.0 / 1.x
  • Leaflet
  • ArcGIS 10.x / MapInfo 10.0
  • Cartes & Données 6.x
  • Mapserver / OpenLayers
qgis esri leaflet database

Mobile Data Collect

  • ODK Collect 1.15
  • GeODK Collect
  • XLSForm
odk_collect odk_collect_2 mobile

Developement

  • Python
  • PHP
  • HTML5/Javascript
  • VBA
python js database

Project Management

  • Consulting
  • Agile developement
  • Requirements analysis / Writing specifications
  • Monitoring Project
project

benoit perceval ©

Contact

benoit.perceval@hotmail.fr



+33 6 95 48 92 87



2 route de Saint-Aubin, 14610 BASLY, France



   Me on LinkedIn


benoit perceval<\ © >