Use POSTGIS to store Real Estate data. Is the market going up/down ?

Is the market going up or down? 2020 has been an uncommon year for several economic sectors and as well in real estate. How much uncertainty and lock down measures have impacted the transactions?
In this post I take as example the Departement 06 — Region Paca France to describe 3 important trends prices for Apartments, Villas and Constructible Land.

Region Paca

All the data used in this tutorial are available thanks to the Open Data France and freely available at https://www.data.gouv.fr/fr/
To easily fetch the data and perform the stats can download as explained below (data are around 3Gb , prerequisites: docker/docker-compose):

git clone git@github.com:sinaure/realestate.git
docker-compose up -d

follow the original post at:

http://claveblanca.ddns.net/2021/04/03/use-opendata-for-realestate-market-analysis/

Navigate to the browser to localhost:8091 and use test@gmail.com/test1234 to login.
You have now access to all the schemas where datas are stored by departament number in different schemas.
Go to dvf schema and check you can find the table mutation where all the transactions are listed.

select  distinct libtypbien from dvf.mutation;| "TERRAIN VITICOLE"                          |
| "BATI - INDETERMINE : Vente avec volume(s)" |
| "BATI - INDETERMINE : Vefa sans descriptif" |
| "TERRAIN LANDES ET EAUX" |
| "APPARTEMENT INDETERMINE" |
| "TERRAIN D'EXTRACTION" |
| "TERRAIN ARTIFICIALISE MIXTE" |
| "BATI MIXTE - LOGEMENT/ACTIVITE" |
| "DES MAISONS" |
| "DES DEPENDANCES" |
| "TERRAIN FORESTIER" |
| "TERRAIN D'AGREMENT" |
| "DEUX APPARTEMENTS" |
| "TERRAIN DE TYPE RESEAU" |
| "UN APPARTEMENT" |
| "TERRAIN NATUREL MIXTE" |
| "UNE MAISON" |
| "ACTIVITE" |
| "TERRAIN DE TYPE TERRE ET PRE" |
| "TERRAIN VERGER" |
| "MAISON - INDETERMINEE" |
| "UNE DEPENDANCE" |
| "TERRAIN AGRICOLE MIXTE" |
| "TERRAIN DE TYPE TAB" |
| "BATI MIXTE - LOGEMENTS" |
| "TERRAIN NON BATIS INDETERMINE" |

Let’s say I am looking for a villa (libtypbien = UNE MAISON) with a big garden (at least 1 hectar) around the Saint Cassien Lake in the Var (region Paca, France) in the 2020 only.
Go to google maps and find a position as longitude / latitude and perform the following query:

select  c1.nom as nom,
m1.valeurfonc,
m1.sterr as sterr,
ST_X(ST_Transform(ST_Centroid(m1.geomparmut), 4326)) as long,
ST_Y(ST_Transform(ST_Centroid(m1.geomparmut), 4326)) as lat,
ST_Distance(
ST_Transform(ST_SetSRID(ST_MakePoint(6.797508148628871, 43.58494915673818),4326),3857),
ST_Transform(ST_Centroid(m1.geomparmut), 3857))/1000 as distance_km
from dvf.mutation m1 JOIN public."communes-20210101" c1 ON m1.l_codinsee[1] = c1.insee
and anneemut = 2020
and libtypbien = 'UNE MAISON'
and sterr >10000
ORDER BY distance_km, anneemut
limit 20;

The results are listed and ordered by growing distance and is indicated the surface of the garden (sterr), the value of the transaction (valeurfonc).

| Common                     | valeurfonc |sterr  | distance km        |
|----------------------------|------------|-------|--------------------|--------------------|--------------------|
| "Callian" | 950000.0 | 10571 | 8.432225405836189 |
| "Saint-Cézaire-sur-Siagne" | 192500.0 | 13070 | 9.829818259653633 |
| "Saint-Cézaire-sur-Siagne" | 345000.0 | 13307 | 11.520406132686697 |
| "Auribeau-sur-Siagne" | 1284050.0 | 10689 | 13.052968902882744 |
| "Grasse" | 300000.0 | 44416 | 14.465240131660936 |
| "Mandelieu-la-Napoule" | 790000.0 | 14505 | 14.967933847198305 |
| "La Roquette-sur-Siagne" | 2059800.0 | 20598 | 15.533549161765468 |
| "Mons" | 350000.0 | 20527 | 17.12626583722745 |
| "Mougins" | 450000.0 | 49385 | 19.61875087299727 |
| "Grasse" | 1900000.0 | 38547 | 21.646000619357842 |
| "Grasse" | 3700000.0 | 38039 | 21.77615947824025 |
| "Grasse" | 700000.0 | 12881 | 22.234301635591343 |
| "Mouans-Sartoux" | 1873000.0 | 10563 | 22.512113800124588 |
| "Roquebrune-sur-Argens" | 2055200.0 | 20604 | 23.12162442561393 |
| "Valbonne" | 600000.0 | 11172 | 25.583864069972293 |
| "Callas" | 385000.0 | 27717 | 26.575645796620744 |
| "Vallauris" | 1020000.0 | 11085 | 29.06868311456359 |
| "Antibes" | 827000.0 | 11006 | 30.13699130464344 |
| "Séranon" | 275000.0 | 17315 | 31.176528128786853 |
| "Antibes" | 440000.0 | 46112 | 32.04983696116396 |

Now we analyse the transactions trend compared to 2019. The Postgresql lag function allow to compute the difference in the total transactions number and overall values between 2019 and 2020 grouping them by type and name of city.

CREATE TABLE mutations_trend AS (
SELECT
bucket,
nom,
libtypbien,
sum_val_fonc, total, sum_sterr, sum_sbati,
sum_val_fonc-lag(sum_val_fonc) over (partition by nom,libtypbien order by bucket) as increase_val,
total-lag(total) over (partition by nom,libtypbien order by bucket) as increase_tx
from mutations_stats_summary_yearly
GROUP BY bucket, nom,libtypbien, sum_val_fonc, total, sum_sterr, sum_sbati
ORDER BY bucket DESC,nom ASC, increase_val ASC, increase_tx ASC);

Cannes shows the worst performance with a decrease of almost 300 Milion Euro in overall volume and more than 1000 transactions less than 2019.

select nom, 
ROUND(sum_val_fonc/1000000,1) as sum_val_fonc,
ROUND(increase_val/1000000,1) as increase_val,
total,
increase_tx
from mutations_trend
where libtypbien = 'UN APPARTEMENT' and bucket = '2019-12-29'
order by increase_val

It looks like the more impacted zones were the coastal where the role of tourism is strong. (valchang is the change on the overall volumes of transactions between 2019 and 2020, txchang the change in the number of transactions)

| City                   | Sum_val|valchang|tx num|txchang|
|------------------------|--------|--------|------|-------|
| "Cannes" | 570.3 | -297.3 | 1669 | -1005 |
| "Saint-Raphaël" | 20.3 | -207.1 | 100 | -961 |
| "Fréjus" | 27.8 | -160.8 | 144 | -932 |
| "Antibes" | 366.8 | -152.9 | 1466 | -608 |
| "Toulon" | 256.9 | -107.4 | 1798 | -832 |
| "Mandelieu-la-Napoule" | 103.3 | -91.6 | 454 | -368 |
| "Cagnes-sur-Mer" | 139.3 | -85.6 | 655 | -421 |
| "Nice" | 1771.2 | -78.2 | 7501 | -704 |
| "Sainte-Maxime" | 9.2 | -67.5 | 43 | -318 |
| "Cavalaire-sur-Mer" | 5.5 | -52.6 | 31 | -258 |
| "Villeneuve-Loubet" | 71.4 | -46.5 | 287 | -184 |
| "Vallauris" | 86.5 | -46.5 | 459 | -210 |
| "Six-Fours-les-Plages" | 73.8 | -46.4 | 383 | -226 |
| "Le Cannet" | 148.0 | -42.2 | 730 | -247 |
| "Saint-Tropez" | 7.2 | -39.2 | 20 | -94 |
| "Saint-Laurent-du-Var" | 101.4 | -39.1 | 426 | -175 |
| "Draguignan" | 4.8 | -38.8 | 49 | -353 |
| "La Seyne-sur-Mer" | 90.8 | -37.8 | 606 | -276 |
| "Grasse" | 80.2 | -25.3 | 501 | -211 |

To sum up:

Appartaments VERY BAD!
Maisons/Villas BAD!
Land VERY GOOD!

Hope you enjoy my thread and could this help you to address your informed investments and let you aware that Open Data give the rights to be informed to everyone wants to!

Feel free to use the dasboard: http://claveblanca.ddns.net:3000/d/lzACEJlMk/real-estate-chart?orgId=1

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store