Vjylku

Buying a "Used" Router

For the Circle of Spores druid's Halo of Spores feature, is your reaction used regardless of whether the other creature succeeds on the saving throw?

Question: "Are you hungry?" Answer: "I feel like eating."

Log to console in a Lightning Web Component

Why is Bernie Sanders maximum accepted donation on actblue $5600?

Is there any danger of my neighbor having my wife's signature?

Why don't you get burned by the wood benches in a sauna?

Isn't a semicolon (';') needed after a function declaration in C++?

Why is quixotic not Quixotic (a proper adjective)?

What did Putin say about a US deep state in his state-of-the-nation speech; what has he said in the past?

Can I legally make a website about boycotting a certain company?

What does it mean when an external ID field follows a DML Statement?

What does "don't have a baby" imply or mean in this sentence?

Why are `&array` and `array` pointing to the same address?

Exploding Numbers

Is it possible to detect 100% of SQLi with a simple regex?

What is an explicit bijection in combinatorics?

Why do we divide Permutations to get to Combinations?

Why Doesn't It Completely Uninstall?

How does the spell Slow affect freefall?

Have any astronauts or cosmonauts died in space?

Integral problem. Unsure of the approach.

In the Lost in Space intro why was Dr. Smith actor listed as a special guest star?

How can changes in personality/values of a person who turned into a vampire be explained?

SpatiaLite not complete data on pt_addresses?

Open Street Map and PostgreSQL what do I exactly need?How are housenumbers and streets linked in a OSM based PostGIS DB?Help building SQL query to get a list of streets of a cityHow to narrow the result from Nominatim with only cities and countries, no additional areas with the same name?Missing some lines after import of .PBF int PostGISIs it possible to get an entire street via a single Overpass API query?Downloading OpenStreetMap data with street names and city attributes for ArcGIS Desktop?OpenLayers 3 - Adding layer with administrative labels and bordersFull address from OSM dataItaly, road network shapefile with full speed information

0

I have a problem.
Imported OSM in SQLite using spatialite_osm_map
Samples query

$select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Is ok, is my street

select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Same is ok, is my street again

select id,sub_type,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from pg_building limit 1;

33042016|yes|Bloc D2|6.50190449190996

Is ok, my address is "Strada Muntenia Bloc D2"

I want to get the rest of data for my address, my city is Hunedoara

select id,country,city,postcode,street,housename,housenumber from pt_addresses where city like 'Hun%';

NULL

?!?!, no city Hunedoara in pt_addreses ??!?!

select id,country,city,postcode,street,housename,housenumber from pt_addresses where housename like '%D2%' or housenumber like '2A%' limit 1;

701843031|RO|Deva|330051|Aleea Militarilor||2a

Something, but wrong city and street.
I must understand Hunedoara is missing from pt_addresses ?

openstreetmap

share|improve this question

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know the scheme of your database. However in OSM Hunedoara is mapped as a town, not as a city. So maybe you need to replace city with town?
  
  – scai
  Jan 2 at 11:23
  
  
  

  
  
  
  

add a comment | 

0

I have a problem.
Imported OSM in SQLite using spatialite_osm_map
Samples query

$select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Is ok, is my street

select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Same is ok, is my street again

select id,sub_type,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from pg_building limit 1;

33042016|yes|Bloc D2|6.50190449190996

Is ok, my address is "Strada Muntenia Bloc D2"

I want to get the rest of data for my address, my city is Hunedoara

select id,country,city,postcode,street,housename,housenumber from pt_addresses where city like 'Hun%';

NULL

?!?!, no city Hunedoara in pt_addreses ??!?!

select id,country,city,postcode,street,housename,housenumber from pt_addresses where housename like '%D2%' or housenumber like '2A%' limit 1;

701843031|RO|Deva|330051|Aleea Militarilor||2a

Something, but wrong city and street.
I must understand Hunedoara is missing from pt_addresses ?

openstreetmap

share|improve this question

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

bumped to the homepage by Community♦ 3 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I don't know the scheme of your database. However in OSM Hunedoara is mapped as a town, not as a city. So maybe you need to replace city with town?
  
  – scai
  Jan 2 at 11:23
  
  
  

  
  
  
  

add a comment | 

I have a problem.
Imported OSM in SQLite using spatialite_osm_map
Samples query

$select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Is ok, is my street

select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

Same is ok, is my street again

select id,sub_type,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from pg_building limit 1;

33042016|yes|Bloc D2|6.50190449190996

Is ok, my address is "Strada Muntenia Bloc D2"

I want to get the rest of data for my address, my city is Hunedoara

select id,country,city,postcode,street,housename,housenumber from pt_addresses where city like 'Hun%';

NULL

?!?!, no city Hunedoara in pt_addreses ??!?!

select id,country,city,postcode,street,housename,housenumber from pt_addresses where housename like '%D2%' or housenumber like '2A%' limit 1;

701843031|RO|Deva|330051|Aleea Militarilor||2a

Something, but wrong city and street.
I must understand Hunedoara is missing from pt_addresses ?

openstreetmap

share|improve this question

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

$select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

select id,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from roads limit 1;

564|Strada Muntenia|26.944248068482

select id,sub_type,name,min(st_distance(geometry,st_point(22.908082,45.763242),0)) from pg_building limit 1;

33042016|yes|Bloc D2|6.50190449190996

select id,country,city,postcode,street,housename,housenumber from pt_addresses where city like 'Hun%';

NULL

select id,country,city,postcode,street,housename,housenumber from pt_addresses where housename like '%D2%' or housenumber like '2A%' limit 1;

701843031|RO|Deva|330051|Aleea Militarilor||2a

share|improve this question

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

share|improve this question

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

edited Dec 31 '18 at 13:06

Vince

14.6k32748

edited Dec 31 '18 at 13:06

Vince

14.6k32748

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

asked Dec 31 '18 at 12:04

Romeo PetrucaRomeo Petruca

1

1 Answer
1

active

oldest

votes

0

Solved,

curl https://download.geofabrik.de/europe/romania-latest.osm.bz2 --output romania-latest.osm.bz2
bzcat romania-latest.osm.bz2 | bin/osmosis --read-xml enableDateParsing=no file=- --bounding-box top=46.104185 left=22.744446 bottom=45.500572 right=24.245453 --write-xml file=- | bzip2 > hunedoara.osm.bz2

spatialite_osm_raw -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_map -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_net -o hunedoara.osm -d hunedoara.sqlite -T roads -m
spatialite_network -d hunedoara.sqlite -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data --overwrite-output
spatialite hunedoara.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'

And now database is complete.
SELECT
way_id, sub, k, v
FROM osm_way_tags AS ways_ref
WHERE
(
ways_ref.way_id IN (33042016)
);

---

33042016 0 name Bloc D2
33042016 1 source Hunedoara Town Hall map
33042016 2 building yes
33042016 3 addr:street Strada Muntenia
33042016 4 addr:housename D2
33042016 5 addr:housenumber 2A

---

share|improve this answer

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "79"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f307293%2fspatialite-not-complete-data-on-pt-addresses%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

Solved,

curl https://download.geofabrik.de/europe/romania-latest.osm.bz2 --output romania-latest.osm.bz2
bzcat romania-latest.osm.bz2 | bin/osmosis --read-xml enableDateParsing=no file=- --bounding-box top=46.104185 left=22.744446 bottom=45.500572 right=24.245453 --write-xml file=- | bzip2 > hunedoara.osm.bz2

spatialite_osm_raw -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_map -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_net -o hunedoara.osm -d hunedoara.sqlite -T roads -m
spatialite_network -d hunedoara.sqlite -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data --overwrite-output
spatialite hunedoara.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'

And now database is complete.
SELECT
way_id, sub, k, v
FROM osm_way_tags AS ways_ref
WHERE
(
ways_ref.way_id IN (33042016)
);

---

33042016 0 name Bloc D2
33042016 1 source Hunedoara Town Hall map
33042016 2 building yes
33042016 3 addr:street Strada Muntenia
33042016 4 addr:housename D2
33042016 5 addr:housenumber 2A

---

share|improve this answer

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

add a comment | 

0

Solved,

curl https://download.geofabrik.de/europe/romania-latest.osm.bz2 --output romania-latest.osm.bz2
bzcat romania-latest.osm.bz2 | bin/osmosis --read-xml enableDateParsing=no file=- --bounding-box top=46.104185 left=22.744446 bottom=45.500572 right=24.245453 --write-xml file=- | bzip2 > hunedoara.osm.bz2

spatialite_osm_raw -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_map -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_net -o hunedoara.osm -d hunedoara.sqlite -T roads -m
spatialite_network -d hunedoara.sqlite -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data --overwrite-output
spatialite hunedoara.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'

And now database is complete.
SELECT
way_id, sub, k, v
FROM osm_way_tags AS ways_ref
WHERE
(
ways_ref.way_id IN (33042016)
);

---

33042016 0 name Bloc D2
33042016 1 source Hunedoara Town Hall map
33042016 2 building yes
33042016 3 addr:street Strada Muntenia
33042016 4 addr:housename D2
33042016 5 addr:housenumber 2A

---

share|improve this answer

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

add a comment | 

Solved,

curl https://download.geofabrik.de/europe/romania-latest.osm.bz2 --output romania-latest.osm.bz2
bzcat romania-latest.osm.bz2 | bin/osmosis --read-xml enableDateParsing=no file=- --bounding-box top=46.104185 left=22.744446 bottom=45.500572 right=24.245453 --write-xml file=- | bzip2 > hunedoara.osm.bz2

spatialite_osm_raw -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_map -o hunedoara.osm -d hunedoara.sqlite
spatialite_osm_net -o hunedoara.osm -d hunedoara.sqlite -T roads -m
spatialite_network -d hunedoara.sqlite -T roads -g geometry -c cost -t node_to -f node_from -n name --oneway-fromto oneway_fromto --oneway-tofrom oneway_tofrom -o roads_net_data --overwrite-output
spatialite hunedoara.sqlite 'CREATE VIRTUAL TABLE "roads_net" USING VirtualNetwork("roads_net_data")'

And now database is complete.
SELECT
way_id, sub, k, v
FROM osm_way_tags AS ways_ref
WHERE
(
ways_ref.way_id IN (33042016)
);

---

33042016 0 name Bloc D2
33042016 1 source Hunedoara Town Hall map
33042016 2 building yes
33042016 3 addr:street Strada Muntenia
33042016 4 addr:housename D2
33042016 5 addr:housenumber 2A

---

share|improve this answer

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

share|improve this answer

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1

answered Jan 23 at 6:41

Romeo PetrucaRomeo Petruca

1