Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Finally Getting Broadband – Via 4G! (cable-free, fast-ish internet)

I live in a field. Well, I live in a house, but it and a few other houses make up a tiny hamlet surrounded by fields. My back garden is itself a small field. It’s nice to live surrounded by countryside, but one of the drawbacks of this rural idyll is our internet connection. Our house is connected to the World Wide Web by a copper telephone line (via a British Telecom service), and it’s not a very good copper telephone line either. I joke that wet, hairy string would be just as useful. Our connection speed is, at best, about 5Mbps download and 0.4Mbps upload. That is not a typo – nought point four megabits a second. My dual ISDN line back in 2003 could just about manage that. At busy times it’s worse – a lot worse – as all the houses in our hamlet contend for whatever bandwidth we have back to civilisation. Evenings & weekends it has almost become a not-service. It is not broadband, it’s narrowband. I pay £23 a month for the wire charge & unlimited calls, another £25 a month for the calls beyond “unlimited” (yeah, tell me) and £30 a month for the narrowband connection. Ouch!

 

My neighbours have complained to BT about our internet speed many times over the years and I did also. I was told (and I paraphrase) “you are at the end of a long piece of old copper-wire-based technology and there are not enough people living there for us to care about, so it is not changing”. Which to me utterly sums up my 30 or so years experience of British Telecom, a company I loath with a passion. I asked if I could get a discount for such a shit service and was told I was on their cheapest deal already. “Would I get better service if I paid more?” No. Well, at least they were honest about that.

About 2 years ago a company called Gigaclear came down the road to our little, rural hamlet. They are being paid a lot of money by Essex county council to lay fibre cable to rural locations all over the district. This raised our hopes. Gigaclear dug channels, lay down the ducting, put a green telecommunications box in place by “Joe on the Donkey” (this is the real name of a neighbour’s house) – and went away. They’ve been back a few times since, but the promised super-mega-fast fibre broadband service they touted has not come to fruition. The last two visits have been to check out why they can’t connect anyone. It might partly be that one pair could not even understand that the green box 100 meters away is probably the one that is supposed to service our house, not the one way across two fields that they have not dug a channel from.

 

I first realised there was another solution when, forced by evenings & weekends when download speeds dropped to below 1Mbps, I started using my iPhone as a hotspot for my laptop. 5/0.4Mpps was replaced by 15/2.0Mbps. I was soon using my phone to upload pictures to social media and the charity I foster cats for, plus for video conferencing for work & social purposes. If my mobile phone was giving me better connection speed, why in hell was I using an expensive & slower connection from my physical telephone line? One problem was I only have so much download allowance on my mobile phone (10GB). The other was you need to keep the mobile by the computer to tether it. It was not a mobile anymore!

I was then chatting to a neighbour and he said he’d tried a relative’s 4G broadband from EE – EE is about the only phone network we can get a decent signal with here and we use them for our mobile phones – and he was pleasantly surprised at the speed. He said it was expensive though…

As a result of this chat I did a quick check on the EE website. A 4G EE broadband device (basically a box with the electronics for a mobile phone and a router in it) would be cheaper than my current BT solution! £35 a month, no up-front fee, and their advertising blurb claimed 31Mbps on average “in some places”. I had no expectation of getting anything near that sales-pitch speed, but repeated speed test on my EE mobile phone was confirming 15 Mbps download and 2 Mbps upload usually, much better than the BT landline. And the offerings of Gigaclear, should they ever plumb us in, was for 30Mbps for a similar cost to EE 4G broadband, and 100Mbps if you spent more spondoolies. All in all, EE seemed not that expensive really and, as I said, cheaper than sod-all bandwidth with BT!

The last thing I checked was if you could get a physical EE 4G signal booster if your signal was poor. Yes, you can, but from 3rd party companies for about £250-£400. Our EE signal in Widlake Towers is better than any other mobile phone operator but it is never an all-bars signal.

The Change to 4G, cable-free Broadband

I decided it was worth a risk. All I wanted was the speed my iPhone was getting and, if it was poorer, a one-off spend of maybe £300 would help matters. I ordered an EE 4G router and 200GB of data a month. Why so much data? Well, I had never exceeded my 10GB data a month on my mobile phone, I am not what you could call a big data user – I do not download films or live stream stuff. But my connection speed had been so bloody awful for so long I had never even dreamed of downloading 1/2 hour TV programs, let alone whole movies! Maybe I might with a better connection. And I was about to start running training courses for a client. I figured I would bet on the safe side.

My EE 4G router turned up the next day, I was so excited!

It was broken. It would get the 4G signal no trouble but it’s wifi service was unstable, it shut down under load. It was so annoying as for 10 minutes I had FORTY Mbps download and FIFTEEN Mbps upload performance! I count this as mental cruelty, to let me see the sunny uplands of normal 1st world internet access but to then immediately remove it from me…

It was clearly a faulty unit so I was straight on to EE. It took over an hour and a half to contact & talk through the issue with EE but, to be fair, they have a process to go through and they have to make sure the customer is not doing something daft like keeping the router in a basement, and they sent me a replacement unit the very next day.

It arrived. I plugged it in. It worked. It’s was great! The bandwidth was an order of magnitude better than the old BT router over the fixed telephone cable. Not only that, it also far exceeded both what I had got via my phone and also the estimates of EE. I got over 60Mbps download on average and often above 70 Mbps. The highest I have seen so far is 98Mbps. Upload averages around 14Mpbs and has gone up to 30 Mbps at times – but I have to say I see the peak upload when download is a little depressed. On average I am now getting consistently over 60Mbps download and 10Mbps upload speeds, though sometimes when the local network is busy (mid workday afternoon) I see a little less. “Peak performance” is weekend and evening times, I get fantastic performance, maybe as business users in the area are quieter and few domestic clients are using the 4G network.

So, over 60Mbps download and 10Mbps upload average and sometimes more – I’ll take that! more than 10 times faster download and, well, 30-50 times faster upload then BT over tired copper.

It’s utterly transformed my online experience. I can honestly say that when I see slow internet performance on web pages now I am just as inclined to blame the remote site as my connection. And I can upload pictures & emails in a way I have never been able to before. Until now I was notable to put up short videos of our foster cats to the charity website unless I did it on my phone in the garden, and that was hit-and-miss. Now I can just chuck videos over to them and not worry about it. For me it is a game changer.

My 4G Choice

I had little choice but to go for EE as no other mobile phone company has decent coverage in my area. You may also have only 1 choice but, it you live in an area where many 4G services are available (i.e. you live in a place where other people live!) then look into which is best – not just for speed/cost but also customer service. Many companies are offering wireless 4 and 5G services. Personally I would stick to 4G as 5G is still shiny and new enough to come with a price hike for not-a-lot more total throughput. I’ve always been really pleased with EE customer service. For years I’ve popped over to one of the two local-ish EE shops whenever I have needed to change something or had a problem and they always sort me out quickly. Not only that, on a couple of occasions I’ve suggested I go for a more expensive plan (say to get more roaming data allowance) and they have looked at my historic usages – “Mate, you’ve never been even close to the smaller plan, save yourself £10 a month and take the cheaper one. You can always upgrade”.

I went for EE’s standard 4G Home Router as the only difference I could see with it and their 4G Home Router 2 was the Home Router 2 supported 64 devices not 32, for an extra £50 up front.. Between us Mrs Widlake and I have fewer than 10 devices, let alone over 32…. At the time of writing there is no initial charge for the 4G Home Router, just a £35-£55 monthly charge depending on what data allowance you want £35=100GB, you get an extra 100GB for each additional £5 up to 500GB but then at £55 it becomes unlimited. You can choose between 18 month contract or no contract and an up-front fee, but go look at the website for details, it will have changed by the time you look (I know this as they have introduced a 5G home router between the time I started this blog post an ended it! But I have no 5G signal so of no consideration for me).

Initially I had the EE 4G home router in the main room of the house so I could fiddle with it if needed, but I soon moved it upstairs to a bedroom where prior tests had shown I got a better 4G signal. (You want as little in the way of building materials and geography between you and the 4G mast, so upstairs by a window is ideal. And in my house the top floor where I put the router is made of wood, straw, mud, & horse shit. Other parts have fully insulated plasterboard which includes a thin metal foil layer to both reflect heat and, unfortunately, block electromagnetic radiation).

Spreading The Network

Another consideration for me was allowing the wifi signal to get to the study. The study is above the garage, a structure covered in black clapperboard which is strangely attached to the neighbour’s house (this is the sort of thing you get with properties hundreds of years old – things just got built). A few years ago when we had the study/garage rebuilt to modern standards we got another company to provide telephone services to the study, to see if it was better than BT. It was. A bit. And it still is. But that company is now part of BT (as is EE to be fair) and is slower than my mobile phone. If the new router reached the study we could stop using BT AND we could stop using this backup supplier (which was cheaper than BT but more limited in some respects). With line-of-sight I hoped the wifi would reach the study. It did  – but it was right at the range limit and the signal would drop :-(. If you moved your laptop or tablet away from the window and clear line-of-site, you lost the Wifi signal from the new 4G broadband router.

Well, I had a possible solution to this too.

There are many wifi extenders on the market at many prices, some just use wifi and some use your power cables and others create a mesh. If 30 years in I.T. have taught me anything it is that there is something deficient in my head that means I personally have no affinity for networks. I need simple. I knew I could not use a power cable solution. With these you plug one device in a socket and it communicates over your domestic power lines to a second plugged-in device which provides a wifi service. For historical reasons my study is on a different power circuit to the house, I doubt it would work. I did not want to go to Mesh as I felt (based on experience) I would fuck it up. I just wanted a simple, single WiFi extender.

After a few hours on the internet I came to the conclusion that there was a solution, a relatively old device (first sold in 2016) that might be what I wanted. A TP Link RE450, also known as an AC1750. It was simple and excelled at having a long range. I ordered one for £50 quid.

It came and, following the simple instructions and maybe half an hour of my part-time attention, I had it working and connecting to both the 5 and 2.4 GHz networks of my EE 4G broadband router. I moved the TP Link RE450 over to the study and plugged it in so it had line-of-site to my EE 4G router. The connection light flashed blue and red, which suggested it was not happy – but I worked out that it was happy with the 2.4Ghz connection but not the 5Ghz one. It was right on the edge of it’s range. A bit of fiddling of orientation (hat tip to Mrs W who realised it was better on it’s side) over 2 days, including moving the router a whole 30cm closer, and now both are happy.

The end result is I now have access to the 4G EE broadband router in the study & garage at about 20Mbps download and 12 Mbps upload. I think the limit is the TP Link to EE router connection, which is just down to distance. Bottom line, I now have access to the internet from every part of my house and separate study, and the whole front garden, and the edge of the field opposite the house, and some of the back garden, at speeds substantially faster than my old landline.

British Telecom will be getting a cancellation notice from me by the end of the month (I need to change some email addresses) and the third party service to the study will also be terminated. I will replace a service from BT that was costing me £80 a month and another that was £30 a month with just one at £40 a month, which gives me a much, much better service.

That feels good.

Latest speed test? Done as I completed this post, I recorded 77Mbps download & 30Mbps upload, which I am incredibly pleased with. I don’t expect to get that all the time though.

Video : Using Expressions in Initialization Parameters in Oracle Database 21c

In today’s video we demonstrate using expressions in initialization parameters, introduced in Oracle database 21c.

The video is based on this article.

The star of today’s video is Deiby Gómez, who is a fellow Oracle ACE Director, and was kind enough to take me sightseeing when I visited Guatemala for a conference.

Cheers

Tim…

The post Video : Using Expressions in Initialization Parameters in Oracle Database 21c first appeared on The ORACLE-BASE Blog.


Video : Using Expressions in Initialization Parameters in Oracle Database 21c was first posted on February 15, 2021 at 9:18 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Is Your Linux Version Hiding Interrupt CPU Usage From You?

TL;DR: Some Linux distros and even different kernel versions within a distro have disabled IRQ time accounting. In such case, your monitoring tools will report zero time spent in hardware interrupt handlers (shown as %hi, %irq, hiq, etc). It’s easy to check how your kernel is behaving by looking at /proc/stat and you can still measure interrupt CPU usage using perf and a little creativity.
Index Index Problem Explanation Kernel configuration options Measuring IRQ time with perf Different distros and kernel versions Summary Here’s a reminder that your Linux kernel may be not breaking down hardware interrupt CPU usage separately in its /proc/stat CPU time reporting, depending on the kernel’s compile-time settings.

Is Your Linux Version Hiding Interrupt CPU Usage From You?

TL;DR: Some Linux distros and even different kernel versions within a distro have disabled IRQ time accounting. In such case, your monitoring tools will report zero time spent in hardware interrupt handlers (shown as %hi, %irq, hiq, etc). It’s easy to check how your kernel is behaving by looking at /proc/stat and you can still measure interrupt CPU usage using perf and a little creativity.
Index Index Problem Explanation Kernel configuration options Measuring IRQ time with perf Different distros and kernel versions Summary Here’s a reminder that your Linux kernel may be not breaking down hardware interrupt CPU usage separately in its /proc/stat CPU time reporting, depending on the kernel’s compile-time settings.

PostgreSQL on Linux: what is cached?

By Franck Pachot

.
In a recent tweet I wanted to highlight the importance of knowing what you measure with pgbench, because looking at “Transactions per second” without knowing if you are in shared buffer cache hits, or filesystem cache hit, or storage cache hit, or physical read… is just meaningless:

The podcast is go!

A few weeks back, I posted that I would give podcasting a trial. I’m thrilled to see that the response has been very supportive, with plenty of downloads and listens, so (as you hear at any keynote launch nowadays). “Today, I’m proud to announce…” Smile …that the podcast is now available on the popular podcast platforms.

Just search for “The Spoken Nerd” or use the links below. The plan is to get an episode out every few weeks, consisting of database tech, chit-chat with friends in the tech community, or of course you can ping me on Twitter with anything you’d like me to talk about.

Enjoy the Spoken Nerd on …

Apple Podcasts

Spotify

Google Podcasts

IHeart

Podbean

Spatial Data 2: Convert GPX Track to a Spatial Line Geometry

This blog is part of a series about my first steps using Spatial Data in the Oracle database.  I am using the GPS data for my cycling activities collected by Strava.

Having loaded my GPS tracks from GPX files into an XML type column, the next stage is to extract the track points and create a spatial geometry column.  

Defining Spatial Geometries

Spatial objects are generically referred to as geometries.  When you define one, you have to specify what kind of geometry it is, and what coordinate system you are using. Later when you compare geometries to each other they have to use the same coordinate system. Otherwise, Oracle will raise an error.  Fortunately, Oracle can convert between coordinate systems.

Various coordinate systems are used for geographical data, they are given EPSG Geodetic Parameter Dataset codes.  Oracle supports various coordinate systems.  As well as older definitions, it also has current definitions where the ESPG code matches the Spatial Reference ID (SDO_SRID).  They can be queried from SDO_COORD_REF_SYS.

I will use two different coordinate systems during this series of blogs

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Set lines 150 pages 99
Column coord_ref_sys_name format a35
Column legacy_cs_bounds format a110
select srid, coord_ref_sys_name, coord_ref_sys_kind, legacy_cs_bounds
from SDO_COORD_REF_SYS where srid IN(4326, 27700)
/

SRID COORD_REF_SYS_NAME COORD_REF_SYS_KIND
---------- ----------------------------------- ------------------------
LEGACY_CS_BOUNDS(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------------------------------------
4326 WGS 84 GEOGRAPHIC2D
SDO_GEOMETRY(2003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 3), SDO_ORDINATE_ARRAY(-180, -90, 180, 90))

27700 OSGB 1936 / British National Grid PROJECTED
  • "The World Geodetic System (WGS) is a standard for use in cartography, geodesy, and satellite navigation including GPS". The latest revision is WGS 84 (also known as WGS 1984, EPSG:4326). It is the reference coordinate system used by the Global Positioning System (GPS).  Where I am dealing with longitude and latitude, specified in degrees, especially from GPS data, I need to tell Oracle that it is WGS84 by specifying SDO_SRID of 4326.
  • Later on, I will also be using data for Great Britain available from the Ordnance Survey that uses the Ordnance Survey National Grid (also known as British National Grid) reference system.  That requires SDO_SRID to be set to 27700.

See also:

Creating Spatial Points

I have found it useful to create a packaged function to convert longitude and latitude to a spatial data point.  It is a useful shorthand that I use in various places.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">create or replace package body strava_pkg as 
k_module CONSTANT VARCHAR2(48) := $$PLSQL_UNIT;

----------------------------------------------------------------------------------------------------
function make_point
(longitude in number
,latitude in number)
return sdo_geometry deterministic is
l_module VARCHAR2(64);
l_action VARCHAR2(64);
begin
dbms_application_info.read_module(module_name=>l_module
,action_name=>l_action);
dbms_application_info.set_module(module_name=>k_module
,action_name=>'make_point');

if longitude is not null and latitude is not null then
return
sdo_geometry (
2001, 4326,
sdo_point_type (longitude, latitude, null),
null, null
);
else
return null;
end if;

dbms_application_info.set_module(module_name=>l_module
,action_name=>l_action);
end make_point;
----------------------------------------------------------------------------------------------------
END strava_pkg;
/

strava_pkg.sql

There are two parameters to SDO_GEOMETRY that I always have to specify.

  • The first parameter, SDO_GTYPE, describes the natures of the spatial geometry being defined.  Here it is 2001.  The 2 indicates that it is a 2-dimensional geometry, and the 1 indicates that it is a single point.  See SDO_GEOMETRY Object Type
  • The second parameter, SDO_SRID, defines the coordinate system that I discussed above.  4326 indicates that I am working with longitude and latitude.

XML Namespace

GPS data is often held in GPX or GPS Exchange Format.  This is an XML schema.  GPX has been the de-facto XML standard for the lightweight interchange of GPS data since the initial GPX 1.0 release in 2002.  The GPX 1.1 schema was released in 2004 (see https://www.topografix.com/gpx.asp).  

Garmin has created an extension schema that holds additional athlete training information such as heart rate.

I can extract individual track points from a GPX with SQL using the extract() and extractvalue() functions.  However, I have GPX tracks that use both versions of the Topographix GPX schema (it depends on upon which piece of software emitted the GPX file), and some that also use the Garmin extensions.  

Therefore, I need to register all three schemas with Oracle.  I can download the schema files with wget.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">cd /tmp/strava
wget http://www.topografix.com/GPX/1/0/gpx.xsd --output-document=gpx0.xsd
wget http://www.topografix.com/GPX/1/1/gpx.xsd
wget https://www8.garmin.com/xmlschemas/TrackPointExtensionv1.xsd

Then I can register the files 

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">delete from plan_table WHERE statement_id = 'XSD';
insert into plan_table (statement_id, plan_id, object_name, object_alias)
values ('XSD', 1, 'gpx0.xsd', 'http://www.topografix.com/GPX/1/0/gpx.xsd');
insert into plan_table (statement_id, plan_id, object_name, object_alias)
values ('XSD', 2, 'gpx.xsd', 'http://www.topografix.com/GPX/1/1/gpx.xsd');
insert into plan_table (statement_id, plan_id, object_name, object_alias)
values ('XSD', 3, 'TrackPointExtensionv1.xsd', 'https://www8.garmin.com/xmlschemas/TrackPointExtensionv1.xsd');

DECLARE
xmlSchema xmlType;
res boolean;
BEGIN
FOR i IN (
SELECT object_alias schemaURL
, object_name schemaDoc
FROM plan_table
WHERE statement_id = 'XSD'
ORDER BY plan_id
) LOOP
--read xsd file
xmlSchema := XMLTYPE(getCLOBDocument('STRAVA',i.schemaDoc,'AL32UTF8'));
--if already exists delete XSD
if (dbms_xdb.existsResource(i.schemaDoc)) then
dbms_xdb.deleteResource(i.schemaDoc);
end if;
--create resource from XSD
res := dbms_xdb.createResource(i.schemaDoc,xmlSchema);

-- Delete existing schema
dbms_xmlschema.deleteSchema(
i.schemaURL
);
-- Now reregister the schema
dbms_xmlschema.registerSchema(
i.schemaURL,
xmlSchema,
TRUE,TRUE,FALSE,FALSE
);
END LOOP;
End;
/

3a_register_xml_schema.sql

Then I can query the registered schemas.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Set pages 99 lines 160
Column schema_url format a60
Column qual_schema_url format a105
select schema_url, local, hier_type, binary, qual_schema_url
from user_xml_schemas
/

SCHEMA_URL LOC HIER_TYPE BIN
------------------------------------------------------------ --- ----------- ---
QUAL_SCHEMA_URL
---------------------------------------------------------------------------------------------------------
https://www8.garmin.com/xmlschemas/TrackPointExtensionv1.xsd YES CONTENTS NO
http://xmlns.oracle.com/xdb/schemas/STRAVA/https://www8.garmin.com/xmlsc...

http://www.topografix.com/GPX/1/0/gpx.xsd YES CONTENTS NO
http://xmlns.oracle.com/xdb/schemas/STRAVA/www.topografix.com/GPX/1/0/gp...

http://www.topografix.com/GPX/1/1/gpx.xsd YES CONTENTS NO
http://xmlns.oracle.com/xdb/schemas/STRAVA/www.topografix.com/GPX/1/1/gpx.xsd

Extracting GPS Track Points from GPX

A GPS track is a list of points specifying at least time, longitude, latitude and often elevation.  I can extract all the points in a GPX as a set of rows.  However, I must specify the correct namespace for the specific GPX.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Column time_string format a20
SELECT g.activity_id
, EXTRACTVALUE(VALUE(t), 'trkpt/time') time_string
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/@lat')) lat
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/@lon')) lng
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/ele')) ele
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/extensions/gpxtpx:TrackPointExtension/gpxtpx:hr'
,'xmlns="http://www.topografix.com/GPX/1/1" xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"')) hr
FROM activities g,
TABLE(XMLSEQUENCE(extract(g.gpx,'/gpx/trk/trkseg/trkpt'
,'xmlns="http://www.topografix.com/GPX/1/1" xmlns:gpxtpx="http://www.garmin.com/xmlschemas/TrackPointExtension/v1"'
))) t
Where activity_id IN(4468006769)
And rownum <= 10
/

Activity
ID TIME_STRING LAT LNG ELE HR
---------- -------------------- ------------- ------------- ------- ----
4468006769 2020-12-13T14:31:13Z 51.52963800 -.18753600 30.6 57
2020-12-13T14:31:14Z 51.52963500 -.18753400 30.6 57
2020-12-13T14:31:15Z 51.52964100 -.18753100 30.6 57
2020-12-13T14:31:16Z 51.52964000 -.18752900 30.6 57
2020-12-13T14:31:17Z 51.52963600 -.18752700 30.6 57
2020-12-13T14:31:18Z 51.52963200 -.18752700 30.6 57
2020-12-13T14:31:19Z 51.52962900 -.18752800 30.6 57
2020-12-13T14:31:20Z 51.52962800 -.18752800 30.6 57
2020-12-13T14:31:21Z 51.52962800 -.18752900 30.6 57
2020-12-13T14:31:22Z 51.52962800 -.18753000 30.6 57

I can use this approach to extract all the points from a GPS track and create a spatial line geometry.  I have put the whole process into a packaged procedure strava_pkg.load_activity.

First I need to work out which version of the Topographix schema is in use.  So I can try extracting the creator name with each and see which is not null.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">
IF l_num_rows > 0 THEN
UPDATE activities
SET gpx = XMLTYPE(l_gpx), geom = null, geom_27700 = null, num_pts = 0, xmlns = NULL
WHERE activity_id = p_activity_id
RETURNING extractvalue(gpx,'/gpx/@version', 'xmlns="http://www.topografix.com/GPX/1/0"')
, extractvalue(gpx,'/gpx/@version', 'xmlns="http://www.topografix.com/GPX/1/1"')
INTO l_xmlns0, l_xmlns1;
l_num_rows := SQL%rowcount;
END IF;

Now I can extract all the points in a GPX as a set of rows and put them into a spatial geometry.  I turn each row with two coordinates into two rows with one point each.  Note that longitude is listed before latitude for each point.  I convert the rows into a list using multiset() and finally cast that as a spatial ordinate array. 

Note that the SDO_GTYPE is 2002 (rather than 2001) because it is a line (rather than a single point) on a two-dimensional coordinate system.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">  BEGIN
UPDATE activities a
SET geom = mdsys.sdo_geometry(2002,4326,null,mdsys.sdo_elem_info_array(1,2,1),
cast(multiset(
select CASE n.rn WHEN 1 THEN pt.lng WHEN 2 THEN pt.lat END ord
from (
SELECT rownum rn
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/@lon')) as lng
, TO_NUMBER(EXTRACTVALUE(VALUE(t), 'trkpt/@lat')) as lat
FROM TABLE(XMLSEQUENCE(extract(a.gpx,'/gpx/trk/trkseg/trkpt', 'xmlns="http://www.topografix.com/GPX/1/1"'))) t
) pt,
(select 1 rn from dual union all select 2 from dual) n
order by pt.rn, n.rn
) AS mdsys.sdo_ordinate_array))
, xmlns = 'xmlns="http://www.topografix.com/GPX/1/1"'
WHERE a.gpx IS NOT NULL
And activity_id = p_activity_id;
l_num_rows := SQL%rowcount;
EXCEPTION
WHEN e_13034 OR e_29877 THEN
dbms_output.put_line('Exception:'||sqlerrm);
l_num_rows := 0;
END;

I have found it helpful to simplify the line geometry with sdo_util.simplify(). It removes some of the noise in the GPS data and has resolved problems with calculating the length of lines that intersect with areas.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">  BEGIN
UPDATE activities
SET geom = sdo_util.simplify(geom,1)
WHERE geom IS NOT NULL
And activity_id = p_activity_id;
l_num_rows := SQL%rowcount;
EXCEPTION
WHEN e_13034 THEN
dbms_output.put_line('Exception:'||sqlerrm);
END;

There are a few other fields I also update at this point.  You will see me use them later.

  • NUM_PTS is the number of points in the line geometry.  
  • GEOM_27700 is the result of converting the line to British National Grid reference coordinates.  This helps when comparing it to British boundary data obtained from the Ordnance Survey or other government agencies.
  • MBR is the minimum bounding rectangle for the line.  This is generated to enable me to improve the performance of some spatial queries.  I have found some of the spatial operators to calculate intersections between geometries are quite slow and CPU intensive when applied to GPS tracks and boundary data that both have lots of points.  SDO_GEOM.SDO_MBR simply returns 4 ordinates that define the bounding rectangle.  This can be used to roughly match geometries that might match before doing a proper match.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">  UPDATE activities 
SET num_pts = SDO_UTIL.GETNUMVERTICES(geom)
, geom_27700 = sdo_cs.transform(geom,27700)
, mbr = sdo_geom.sdo_mbr(geom)
WHERE geom IS NOT NULL
And activity_id = p_activity_id
RETURNING num_pts INTO l_num_pts;
dbms_output.put_line('Activity ID:'||p_activity_id||', '||l_num_pts||' points');

Now I can load each GPX and process it into a spatial geometry in one step.  I can process all of the activities in a simple loop.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">set serveroutput on timi on
exec strava_pkg.load_activity(4468006769);
Loading Activity: 4468006769
ACTIVITIES/4468006769.gpx.gz - 1286238 bytes
xmlns 1=StravaGPX Android
Activity ID:4468006769, 998 points

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.41

Now my Strava activities are all in spatial geometries and I can start to do some spatial processing.

Build a Beautiful Site in the WordPress Mobile Apps with Predesigned Page Layouts

Your WordPress mobile app is a convenient way to create and manage your WordPress site. Now, you can design a new page right from your phone or tablet — and build the site of your dreams — with predesigned page layouts.

Introducing starter page layouts

Not all of us are designers, and building a page on your site with the layout in your mind can be intimidating and time-consuming — but it doesn’t have to be that way! Now when you create a new page on WordPress for iOS or Android, you can choose from premade layouts. You can also customize them to fit your needs, right from the block editor.

Choosing a layout

When you create a new page in the app, you’ll see a list of premade page layouts, including about pages, contact pages, team pages, services pages, and more. Whether you’re the owner of an online shop of sustainably made clothing, the founder of a newly formed digital magazine, or a financial strategist who’s just launched a consulting business, you can use these premade layouts to build the most essential pages on your website.

https://en-blog.files.wordpress.com/2021/01/image.png?w=2048 2048w, https://en-blog.files.wordpress.com/2021/01/image.png?w=150 150w, https://en-blog.files.wordpress.com/2021/01/image.png?w=300 300w, https://en-blog.files.wordpress.com/2021/01/image.png?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

Once you find a layout that you’d like to try, tap it to select it. After you’ve selected a layout, you can either preview it or create a new page with the chosen layout.


Ready to try these new Starter Page Layouts? Be sure to update your WordPress app to the latest version. If you don’t have the app yet, download it for free, on both Android and iOS.

We’d love to hear your feedback on these new layouts. Reach out to us from within the app by going to My Site, tapping your photo on the top right, tapping Help & Support,  and then selecting Contact Support.

You don’t need to be an expert to be useful!

https://oracle-base.com/blog/wp-content/uploads/2021/02/4xl53c-300x300.jpg 300w, https://oracle-base.com/blog/wp-content/uploads/2021/02/4xl53c-57x57.jpg 57w" sizes="(max-width: 501px) 85vw, 501px" />

I come from a time when you could be an expert at one thing and be really useful to a company, but I think that time is long gone. If you only have one skill, no matter how good you are at it, you probably can’t achieve anything in tech without waiting weeks to get people to help you with all the stuff you don’t understand. In recent years, being a tech allrounder seems to be much more useful than being an expert. Maybe it always was.

Of course, this poses its own set of problems. How do you learn all this stuff? That’s the hard part and there aren’t any short cuts. There isn’t a “full stack developer course” that will teach you. You’ve just got to work on your Google-Fu and start getting your hands dirty. On the positive side, there is a lot of good information out there to get you started. Blog posts and videos that will get you from zero to adequate in a short amount of time if you put in the effort.

Over the last few years I’ve played with a lot of different technology, and I still find new stuff interesting, but it’s taken me a long time to deal with the fact I’m crap at most of it. Good enough to get the job done and fool people into thinking I know what I’m doing, but ultimately only one weekend of playing with the tech and a couple of Google searches ahead of some other people.

So my advice to people in tech is:

  • Try and get involved in as many aspects of tech as possible.
  • Forget trying to be an expert in any of them. Just try to get good enough to be useful.
  • Be humble enough to realise that what you say and do today may change tomorrow when you’ve Googled a bit more.
  • Try to understand the big picture. How things fit together and how processes work. Programming languages and services change all the time, but understanding the goal and the processes to get there don’t change as much as you might think.

Remember, it’s just my opinion!

Cheers

Tim…

The post You don’t need to be an expert to be useful! first appeared on The ORACLE-BASE Blog.


You don’t need to be an expert to be useful! was first posted on February 11, 2021 at 10:04 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Showcase Your Figma Designs on WordPress P2

Figma — one of the most popular and fastest growing digital design tools today — was recently voted “the most exciting design tool of 2021.” 

In many organizations, a smaller group — often the design team — uses Figma on a daily basis. But designers need a seamless way to share their work and gather feedback from other disciplines across the organization. Enter P2. P2 is a product powered by WordPress.com that boosts remote, asynchronous team collaboration. With P2, team members can share ideas, collect feedback, and assign tasks to one another.

You can now embed Figma files on P2 and get contextual feedback from everyone, creating a more inclusive environment, eliminating the need for others to learn and navigate design-specific software. 

Sharing Figma files on P2 allows teams to review designs and comment where everyone collaborates. It integrates all work in a single spot, helping track project progress. P2 is fully searchable for future reference too! As you iterate in Figma, your files will magically sync on P2. No more messy screen grabs or wondering which Figma file is the most up-to-date.

Step 1 : Copy the link to the art board or prototype from Figma

Step 2: Add the Figma block and paste the link into it

Get P2

Want to know more about how P2 can help improve communication and collaboration on your teams? Check out a demo. You can also create your own P2 here and take it for a spin. Any questions? Feel free to comment on the demo P2.