Postgres 12

CREATE TABLE public."Operation"

(

id integer NOT NULL,

geom geometry(point,4326),

parking_lot_id_x integer,

parking_space_id integer,

xml_id character varying,

latitude double precision,

longitude double precision,

buffer geometry(polygon,4326),

PRIMARY KEY (id)

);

CREATE TABLE public."Residential"

(

id integer NOT NULL,

geom geometry,

full_id character varying(254),

osm_id character varying(254),

osm_type character varying(254),

landuse character varying(254),

PRIMARY KEY (id)

);

INSERT INTO public."Operation" VALUES (1, ST_GEomFromText('POINT(9.693982411815474 52.39380588159547)',4326), 25, 1, 56, '52.39380588159547', '9.693982411815474', null);

INSERT INTO public."Operation" VALUES (2, ST_GEomFromText('POINT(9.687526818702922 52.395648667248025)',4326), 26, 2, 57, '52.395648667248025', '9.687526818702922', null);

INSERT INTO public."Operation" VALUES (3, ST_GEomFromText('POINT(9.674964382966287 52.39767959180928)',4326), 27, 3, 58, '52.39767959180928', '9.674964382966287', null);

INSERT INTO public."Operation" VALUES (4, ST_GEomFromText('POINT(9.675539330691588 52.398176370657986)',4326), 28, 4, 59, '52.398176370657986', '9.675539330691588', null);

INSERT INTO public."Operation" VALUES (5, ST_GEomFromText('POINT(9.675101204830444 52.39990457482717)',4326), 29, 5, 60, '52.39990457482717', '9.675101204830444', null);

INSERT INTO public."Operation" VALUES (6, ST_GEomFromText('POINT(9.678668824587607 52.398387518494005)',4326), 30, 6, 61, '52.398387518494005', '9.678668824587607', null);

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

UPDATE public."Operation" SET buffer = ST_Buffer(geom::geography,100)::geometry;

6 rows affected

select * from public."Operation"

id	geom	parking_lot_id_x	parking_space_id	xml_id	latitude	longitude	buffer
1	0101000020E61000004B7EA5A95163234066362B3B68324A40	25	1	56	52.39380588159547	9.693982411815474	0103000020E610000001000000210000005DDD094512642340C31BBAF267324A40DD721C350E64234067B6EC3462324A403EB450E602642340617264B25C324A40901AEAC7F063234081C955A157324A401EFC2B8CD8632340DDBB983353324A4031587F21BB632340F881BE944F324A40823A49A999632340C20065E84C324A40F8DFCB6C75632340B974D8484B324A4084FC7FD04F63234046C810C64A324A40148562462A632340168414654B324A40D29EBD3F06632340F460C71F4D324A40716BF81EE5622340586126E54F324A40D426F929C8622340AC23EF9953324A4066709F7DB0622340E900AC1958324A40ACDFD1029F622340D9AB1A385D324A4059908A65946223407C8BDFC262324A405DBC3A0E91622340FF17758368324A403D81C61D956223401D3344416E324A40C970406CA0622340B8E0D0C373324A405686718AB262234005FBE5D478324A4053911EC6CA622340D486AA427D324A40F92BE130E862234099278CE180324A408EF250A909632340ECD7EB8D83324A40F5E022E62D6323404E6A7C2D85324A40EE62D18253632340745745B085324A400080500D79632340E0E53F1185324A40543547149D6323406D9F885683324A40D5EC4135BE6323409C2D239180324A40E744522ADB6323401DED52DC7C324A409C0496D6F263234001A98E5C78324A406FEC295104642340EFCE193E73324A40E6A71CEE0E642340F7E850B36D324A405DDD094512642340C31BBAF267324A40
2	0101000020E610000069E7E28303602340A444939DA4324A40	26	2	57	52.395648667248025	9.687526818702922	0103000020E61000000100000021000000303C6621C4602340BD08CE55A4324A408DA24812C0602340D4F0FC979E324A4016DA2FC4B4602340D3836A1599324A40907B58A6A2602340879F4B0494324A40895C006B8A602340B2E378968F324A4038918C006D6023402A5E84F78B324A405FF65F884B60234087F60C4B89324A40826BBC4B27602340680F60AB87324A40551C1CAF0160234083D1762887324A40313C7F24DC5F2340B90F59C787324A4077D5341DB85F234048CCEB8189324A40BA65AAFB965F2340AF452D478C324A40E3BCCD057A5F2340D03CDCFB8F324A400BFC8658625F23406D08847B94324A4068D6C5DC505F2340962AE39999324A40D3C38D3E465F234024A39E249F324A40CF3E59E6425F2340CB4631E5A4324A40D32E15F5465F2340F41304A3AA324A408922DC42525F234028EA9A25B0324A4071F67D60645F2340B23FC036B5324A40B0FBC49B7C5F2340B8799AA4B9324A408BB84E069A5F234062669643BD324A4006F9B47EBB5F23408FFD13F0BF324A40E816ADBBDF5F23408FEBC48FC1324A404F06B05805602340B66AAF12C2324A401390AEE32A6023404277CB73C1324A408DCB4AEB4E602340805134B9BF324A40E8C50A0D70602340DE66ECF3BC324A408788F8028D60234089F1353FB9324A40D75729B0A4602340CCBE86BFB4324A40D7D7B02BB6602340176D21A1AF324A40665794C9C0602340ABED6116AA324A40303C6621C4602340BD08CE55A4324A40
3	0101000020E61000002C9C7DEE94592340C28D342AE7324A40	27	3	58	52.39767959180928	9.674964382966287	0103000020E61000000100000021000000C373658E555A23403C2CBEE3E6324A40C22AE680515A234064CAE525E1324A40B73B4434465A2340F97C3FA3DB324A402ED8AD17345A234060E50092D6324A40938054DD1B5A23403FDB0324D2324A40497F9373FE5923409F0CDC84CE324A40C2D2C6FBDC592340A0592AD8CB324A4030AB2CBFB859234052623E38CA324A4020D83E2293592340CDBB13B5C9324A40718700976D59234053BCB453CA324A4072F7C68E49592340F4E7080ECC324A400CD6086C28592340A3E410D3CE324A4092C5BF740B592340CFA88D87D2324A406EE7E1C5F35823400E790C07D7324A4067936E48E2582340A66A4D25DC324A405BF079A8D75823407AA6F6AFE1324A402D8E8F4ED4582340BBB48370E7324A400F28AD5BD8582340CBCA5D2EED324A403839FDA7E35823409B8008B1F2324A40BE065EC4F5582340FD884DC2F7324A40AB38A6FE0D59234045115230FC324A40E3207D682B592340664781CFFF324A40596B83E04C592340AC2A397C02334A405222721D71592340D629291C04334A40A196C2BA96592340B912559F04334A406E966246BC592340F85DB20004334A405004EE4EE0592340F2C9594602334A40B8BBE171015A23407C5C4B81FF324A40E1F13B691E5A2340291AC7CCFB324A4017E90318365A23406AE2404DF7324A402D9F3D95475A23408AC0F92EF2324A40D3B2DD34525A2340D97C4CA4EC324A40C373658E555A23403C2CBEE3E6324A40
4	0101000020E6100000D6F6884AE05923400D027D71F7324A40	28	4	59	52.398176370657986	9.675539330691588	0103000020E61000000100000021000000A512FDEAA05A2340E828F72AF7324A40831967DD9C5A2340BE261F6DF1324A40B8F5A990915A23407DCD79EAEB324A407EE4F4737F5A234028B53CD9E6324A400E947A39675A2340A9A6416BE2324A40279397CF495A2340893C1CCCDE324A40AD2EA957285A23405A3F6D1FDC324A4079E2EE1A045A23408834847FDA324A4046BAE37DDE59234060945CFCD9324A407B048CF2B85923406B97009BDA324A4032F93DEA9459234016A45755DC324A40CD0F71C7735923400844621ADF324A400E7C1FD056592340B953E1CEE2324A4021B33F213F5923402C01624EE7324A40B61FD1A32D592340564FA46CEC324A40F4B9E70323592340AF594EF7F1324A4038A30EAA1F59234079A0DBB7F7324A40A0EC42B723592340ED56B575FD324A403932AE032F592340A2185FF802334A407DAD2D2041592340CDA1A20908334A40A2D8965A595923407D2EA5770C334A4024C18FC4765923402400D21610334A406DC4B73C985923409C2D87C312334A40F1A0C679BC5923404540746314334A40356B3417E2592340BC229DE614334A40BCD0EDA2075A23406B6BF74714334A4037BA8DAB2B5A234049F69B8D12334A40923990CE4C5A234083E58AC80F334A409CF2F2C5695A2340A25704140C334A40EDD3BC74815A2340A5427C9407334A408BC8F1F1925A234036C4337602334A40FD9D86919D5A234004B285EBFC324A40A512FDEAA05A2340E828F72AF7324A40
5	0101000020E6100000527978DDA6592340A533B71230334A40	29	5	60	52.39990457482717	9.675101204830444	0103000020E61000000100000021000000F2ECDA7F675A234078A23CCC2F334A40CCEF4872635A2340737C640E2A334A4096A17C25585A2340C191BE8B24334A40DAD4A508465A2340F87F807A1F334A40CA83F8CD2D5A23409B19840C1B334A40F232D363105A234065065D6D17334A4091BB95EBEE5923403A1FACC014334A4061A382AECA5923405EFCC02013334A40905F1811A5592340B62A979D12334A400BE65E857F59234057F8383C13334A409630B07C5B59234072E18DF614334A40696D87593A592340B18496BB17334A40CC57E2611D5923401DD213701B334A406B9ABAB2055923400B0993EF1F334A40DA631235F4582340D23AD40D25334A4058E3FF94E9582340DC8D7D982A334A40C6CE0F3BE65823407A890A5930334A408F194048EA582340C363E41636334A403987BA94F5582340F3B68E993B334A4043BC5BB107592340A739D3AA40334A4030E7F7EB1F592340521ED71845334A40D01F33563D592340309905B848334A402737AACE5E592340BFB0BC644B334A40A2E1110C835923408BDBAB044D334A4002CADEA9A859234096EFD6874D334A40E2F5F935CE592340AD6D33E94C334A40E58BFA3EF2592340171CDA2E4B334A40C3E65862135A2340ED07CB6948334A4083220F5A305A2340303C46B544334A401DF82009485A23409D9DBF3540334A40B88E8F86595A23406E3B78173B334A40EE7C4D26645A234072E0CA8C35334A40F2ECDA7F675A234078A23CCC2F334A40
6	0101000020E61000004AD6EC7A7A5B23407E88B95CFE324A40	30	6	61	52.398387518494005	9.678668824587607	0103000020E61000000100000021000000BF07951B3B5C2340C417E015FE324A40254E930D375C2340ACF20958F8324A40F08D6CC02B5C2340869A69D5F2324A40441354A3195C2340137634C4ED324A403B5E8068015C234000004456E9324A40DE6C51FEE35B2340956A2BB7E5324A40F9762786C25B23402F008B0AE3324A404C4244499E5B2340D8B6B16AE1324A40E86C24AC785B2340E36B9AE7E0324A409310CD20535B234029B74E86E1324A40FD6194182F5B2340F05EB540E3324A407206F1F50D5B23405B53CE05E6324A400599DBFEF05A2340E9E359BAE9324A404E3F4850D95A23409EC3E439EE324A402F7433D3C75A23401A912E58F3324A40B0BCAD33BD5A2340171EDDE2F8324A40A2653EDAB95A234070BE6BA3FE324A40D26FDEE7BD5A23401298436104334A403953B334C95A2340E758E8E309334A40323A9651DB5A234058EE23F50E334A40B7CC588CF35A2340ADE21B6313334A4091A89DF6105B234092DF3B0217334A40CC3F016F325B23401D7AE2AE19334A409A0639AC565B234013CBBF4E1B334A40DE7EBB497C5B23401858D8D11B334A409E8A74D5A15B2340445822331B334A400216FFDDC55B2340CC47B77819334A404105D800E75B234064E297B316334A403095FEF7035C234096D304FF12334A406B047CA61B5C2340648C727F0E334A40332E57232D5C2340C68E226109334A40925388C2375C234029FA6FD603334A40BF07951B3B5C2340C417E015FE324A40

INSERT INTO public."Residential" VALUES (1, ST_GeomFromText('POLYGON((9.693588094112373 52.39414543838985, 9.694328501691626 52.39397380868952, 9.694174756472517 52.39362874161061, 9.693445118144536 52.393639872848816, 9.693588094112373 52.39414543838985))', 4326), 1234, 24353, 'relation', 'residential');

INSERT INTO public."Residential" VALUES (2, ST_GeomFromText('POLYGON((9.686685967449211 52.39491983038083, 9.686317248988773 52.396069896070344, 9.687978880110508 52.3962003643716, 9.688455377269856 52.3950186803413, 9.686685967449211 52.39491983038083))', 4326), 4314, 14323, 'relation', 'residential');

INSERT INTO public."Residential" VALUES (3, ST_GeomFromText('POLYGON((9.674918219280315 52.39743120028766, 9.675371462742596 52.39776665654778, 9.675551920787763 52.39768215253932, 9.67507769383186 52.39735437788447, 9.674918219280315 52.39743120028766))', 4326), 4565, 42634, 'relation', 'residential');

INSERT INTO public."Residential" VALUES (4, ST_GeomFromText('POLYGON((9.674448189023135 52.397689834728574, 9.674897235786691 52.39801504618113, 9.675069300434409 52.39794078550942, 9.674620253670852 52.397597648369036, 9.674448189023135 52.397689834728574))', 4326), 4783, 5233, 'relation', 'residential');

INSERT INTO public."Residential" VALUES (5, ST_GeomFromText('POLYGON((9.676827123529273 52.400211368385094, 9.678765554865416 52.39927147912959, 9.677288204613088 52.39818996186542, 9.675327235534237 52.39905473151292, 9.676827123529273 52.400211368385094))', 4326), 7898, 15314, 'relation', 'residential');

1 rows affected

1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE INDEX idx_operation_geom ON public."Operation" USING gist (geom);

CREATE INDEX idx_operation_buffer ON public."Operation" USING gist (buffer);

CREATE INDEX idx_residential_geom ON public."Residential" USING gist (geom);

SELECT

ST_Intersection(ST_MakeValid(r.geom),o.buffer) AS intersection,

ST_Area(ST_Intersection(ST_MakeValid(r.geom),o.buffer))/ST_Area(r.geom)*100

FROM public."Residential" r, public."Operation" o

WHERE ST_Intersects(o.buffer,ST_MakeValid(r.geom));

intersection	?column?
0103000020E6100000010000000500000076E47E067F632340AAE6D7BB6D324A403684A9DF6A632340DAFD356D62324A40D700103D0B632340C52496CA62324A4019378AFA1D63234041E0935B73324A4076E47E067F632340AAE6D7BB6D324A40	100
0103000020E61000000100000005000000C92C76F9645F2340B046196BB2324A40A1D38CC43E602340D5D18BB1B6324A403D4224397D602340FFAFDCF88F324A400AA2994D955F23408603A6BB8C324A40C92C76F9645F2340B046196BB2324A40	100
0103000020E610000001000000050000002443D249CA592340EFA78E04EA324A401D11FDF0E1592340FCADAF3FE7324A40AC8892C8A3592340D6F71C82DC324A4028917EE18E592340A1D28B06DF324A402443D249CA592340EFA78E04EA324A40	100
0103000020E610000001000000050000002443D249CA592340EFA78E04EA324A401D11FDF0E1592340FCADAF3FE7324A40AC8892C8A3592340D6F71C82DC324A4028917EE18E592340A1D28B06DF324A402443D249CA592340EFA78E04EA324A40	100
0103000020E61000000100000005000000B2BA67218C592340FA543328F2324A407CCCEFAEA2592340F6C041B9EF324A4098786DD367592340135BD07AE4324A40CE66E54551592340310E2180E7324A40B2BA67218C592340FA543328F2324A40	99.99999999999999
0103000020E61000000100000005000000B2BA67218C592340FA543328F2324A407CCCEFAEA2592340F6C041B9EF324A4098786DD367592340135BD07AE4324A40CE66E54551592340310E2180E7324A40B2BA67218C592340FA543328F2324A40	99.99999999999999
0103000020E6100000010000000C0000006E6FCD7DC4592340BE94B63914334A407694C602C6592340DAC7B38414334A40356B3417E2592340BC229DE614334A40BCD0EDA2075A23406B6BF74714334A4037BA8DAB2B5A234049F69B8D12334A40923990CE4C5A234083E58AC80F334A409CF2F2C5695A2340A25704140C334A40EDD3BC74815A2340A5427C9407334A408BC8F1F1925A234036C4337602334A40FD9D86919D5A234004B285EBFC324A402BD85FF59D5A234032530C40FC324A406E6FCD7DC4592340BE94B63914334A40	10.450341867467825
0103000020E6100000010000000B0000006E6FCD7DC4592340BE94B63914334A407ACA597B655A23405178284333334A40F2ECDA7F675A234078A23CCC2F334A40CCEF4872635A2340737C640E2A334A4096A17C25585A2340C191BE8B24334A40DAD4A508465A2340F87F807A1F334A40CA83F8CD2D5A23409B19840C1B334A40F232D363105A234065065D6D17334A4091BB95EBEE5923403A1FACC014334A40A965A15BCD592340C744783F13334A406E6FCD7DC4592340BE94B63914334A40	10.40356664490449
0103000020E6100000010000000F000000B598A728875B2340278BEC531B334A403E8F0185C55A23405BE77FE3F7324A40436CC665BD5A2340D6EABAC8F8324A40B0BCAD33BD5A2340171EDDE2F8324A40A2653EDAB95A234070BE6BA3FE324A40D26FDEE7BD5A23401298436104334A403953B334C95A2340E758E8E309334A40323A9651DB5A234058EE23F50E334A40B7CC588CF35A2340ADE21B6313334A4091A89DF6105B234092DF3B0217334A40CC3F016F325B23401D7AE2AE19334A409A0639AC565B234013CBBF4E1B334A40DE7EBB497C5B23401858D8D11B334A409998E72C845B23407F0A81B01B334A40B598A728875B2340278BEC531B334A40	17.711388856353267

ALTER TABLE public."Operation" ADD COLUMN res_percent double precision;

UPDATE public."Operation" SET res_percent = ST_Area(ST_Intersection(ST_MakeValid(r.geom),buffer))/ST_Area(r.geom)*100

FROM public."Residential" r

WHERE ST_Intersects(buffer,ST_MakeValid(r.geom));

6 rows affected

SELECT id,res_percent FROM public."Operation";

id	res_percent
1	100
2	100
3	100
4	100
5	10.40356664490449
6	17.711388856353267

EXPLAIN SELECT * FROM public."Operation";

QUERY PLAN
Seq Scan on "Operation" (cost=0.00..2.12 rows=12 width=132)

EXPLAIN SELECT * FROM public."Operation" where buffer = 2;

ERROR:  operator does not exist: geometry = integer
LINE 1: EXPLAIN SELECT * FROM public."Operation" where buffer = 2; 
                                                              ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

Vaccum

ERROR:  syntax error at or near "Vaccum"
LINE 1: Vaccum 
        ^